Results 1 to 3 of 3

Thread: Query Issues

  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: Query Issues

    I have a query that works perfectly except I um unable to return any zeros in the result set column . When someone creates a bid on a job the result should reflect a 1 and a zero if no one creates a bid on the job in question. Maybe my query is wrong or I am not thinking of something I should be doing:

    The Query:
    SELECT j.jobID, COUNT(j.jobid) AS TotalBids, j.availabilityID, j.City, j.dateCreated, j.countryID, j.Jobdescription, j.zipcode, j.budget, j.hourly, j.fixed, j.stateID, jc.title,
    js.name AS StatusName, a.FullTime, a.partTime, a.AsNeeded, a.WeekendsOnly, jp.ReqTools, jp.ReqTransportation, jp.ReqCertification, jp.ReqMaterials,
    c.countryName, s.stateName, s.stateCode
    FROM tbl_job AS j INNER JOIN
    tbl_job_category AS jc ON j.categoryID = jc.categoryID INNER JOIN
    tbl_job_status AS js ON j.statusID = js.statusID INNER JOIN
    tbl_availability AS a ON j.availabilityID = a.availabilityID INNER JOIN
    tbl_job_preference AS jp ON j.preferenceID = jp.PreferenceID INNER JOIN
    tbl_country AS c ON j.countryID = c.countryID INNER JOIN
    tbl_state AS s ON j.stateID = s.stateID LEFT JOIN
    tbl_job_bid ON j.jobID = tbl_job_bid.jobID
    WHERE (1 = 1) AND (j.isActive = 1)
    GROUP BY s.stateCode, s.stateName, c.countryName, jp.ReqMaterials, jp.ReqCertification, jp.ReqTransportation, jp.ReqTools, a.WeekendsOnly, a.AsNeeded, a.partTime,
    a.FullTime, js.name, jc.title, j.stateID, j.fixed, j.hourly, j.budget, j.zipcode, j.Jobdescription, j.countryID, j.dateCreated, j.City, j.availabilityID, j.jobID

    the result set I am getting is this:
    ID totalbids avail city Date Created
    1 6 6 orlando 2010-08-09 20:38:58.537
    3 5 13 orlando 2010-08-09 23:10:32.007
    5 14 15 orlando 2010-08-10 14:55:41.910
    7 23 17 orlando 2010-08-12 21:26:12.210
    8 1 21 jacksonville 2010-08-14 03:35:59.373


    and it should be something like this:
    ID totalbids avail city Date Created
    1 6 6 orlando 2010-08-09 20:38:58.537
    3 5 13 orlando 2010-08-09 23:10:32.007
    5 14 15 orlando 2010-08-10 14:55:41.910
    7 23 17 orlando 2010-08-12 21:26:12.210
    8 0 21 jacksonville 2010-08-14 03:35:59.373

    Note: the 0 in totalbids column this is where someone created a job and no one has placed a bid on it

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT j.jobID
         , COALESCE(b.bids,0) AS TotalBids
         , j.availabilityID
         , j.City
         , j.dateCreated
         , j.countryID
         , j.Jobdescription
         , j.zipcode
         , j.budget
         , j.hourly
         , j.fixed
         , j.stateID
         , jc.title
         , js.name AS StatusName
         , a.FullTime
         , a.partTime
         , a.AsNeeded
         , a.WeekendsOnly
         , jp.ReqTools
         , jp.ReqTransportation
         , jp.ReqCertification
         , jp.ReqMaterials
         , c.countryName
         , s.stateName
         , s.stateCode
      FROM tbl_job AS j 
    INNER
      JOIN tbl_job_category AS jc
        ON jc.categoryID           = j.categoryID 
    INNER
      JOIN tbl_job_status AS js
        ON js.statusID             = j.statusID 
    INNER
      JOIN tbl_availability AS a
        ON a.availabilityID        = j.availabilityID 
    INNER
      JOIN tbl_job_preference AS jp
        ON jp.preferenceID         = j.preferenceID 
    INNER
      JOIN tbl_country AS c
        ON c.countryID             = j.countryID 
    INNER
      JOIN tbl_state AS s
        ON s.stateID               = j.stateID 
    LEFT OUTER
      JOIN ( SELECT jobID
                  , COUNT(*) AS bids
               FROM tbl_job_bid
             GROUP
                 BY jobID ) AS b
        ON b.jobID                 = j.jobID
     WHERE j.isActive = 1
    notice how i pushed the grouping down one level into the subquery

    makes the outer query way more efficient

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    4

    Talking

    This is exactly what I was looking for. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •