Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Belfast
    Posts
    16

    Question Unanswered: Nested select SQL Statement. help

    I'm trying to return the count of jobs in the jobs table - within a date range.. and bring back all records in another related table (Job Sector).

    Simplified - the results should look like this

    jobName jobCount
    Belfast 0
    Denver 15


    Here's what I've got...

    Code:
    SELECT 
    fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL,COUNT(J.jobId) As counter FROM tblRecruitmentSector RD LEFT JOIN tblRecruitmentJob J ON RD.fld_job_sectorId = J.jobSector WHERE (jobPersist = 1 AND (jobStart<=dbo.StripTimeFromDate(GetDate()) AND jobEnd>=dbo.StripTimeFromDate(GetDate()))) OR jobPersist=0  GROUP BY fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL
    Unfortunately because the job count is zero - the join brings back zero records... I think I need a nested select. Which I've tried with the below....

    Code:
    SELECT 
    fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL,
    
    (SELECT COUNT(J.jobId) As counter from tblRecruitmentJob J ON RD.fld_job_sectorId = J.jobSector WHERE (jobPersist = 1 AND (jobStart<=dbo.StripTimeFromDate(GetDate()) AND jobEnd>=dbo.StripTimeFromDate(GetDate()))) OR jobPersist=0)
    
    FROM tblRecruitmentSector

    This however only returns a count (as a whole) rather than per sector.
    i.e.

    jobName jobCount
    Belfast 15
    Denver 15

    Anyone help?
    ---------------------------------------
    Make something idiot proof- and someone will make a better idiot
    ----------------------------------------

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you did not indicate which table the jobPersist, jobStart and jobEnd columns are in

    if they are in the right table of your LEFT OUTER JOIN then you must remove the WHERE clause and move those conditions into the ON clause of the join

    vwalah

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

  3. #3
    Join Date
    Oct 2003
    Location
    Belfast
    Posts
    16
    jobPersist, jobStart and jobEnd are all on the Jobs table... can you mock up how you mean?
    ---------------------------------------
    Make something idiot proof- and someone will make a better idiot
    ----------------------------------------

  4. #4
    Join Date
    Oct 2003
    Location
    Belfast
    Posts
    16
    jobPersist, jobStart and jobEnd are all on the Jobs table... can you mock up how you mean?
    ---------------------------------------
    Make something idiot proof- and someone will make a better idiot
    ----------------------------------------

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    SELECT 
    fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL,COUNT(J.jobId) As counter FROM tblRecruitmentSector RD LEFT JOIN tblRecruitmentJob J ON RD.fld_job_sectorId = J.jobSector WHERE (jobPersist = 1 AND (jobStart<=dbo.StripTimeFromDate(GetDate()) AND jobEnd>=dbo.StripTimeFromDate(GetDate()))) OR jobPersist=0  GROUP BY fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL
    to this --
    Code:
    SELECT 
    fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL,COUNT(J.jobId) As counter FROM tblRecruitmentSector RD LEFT JOIN tblRecruitmentJob J ON RD.fld_job_sectorId = J.jobSector AND ( (jobPersist = 1 AND (jobStart<=dbo.StripTimeFromDate(GetDate()) AND jobEnd>=dbo.StripTimeFromDate(GetDate()))) OR jobPersist=0 ) GROUP BY fld_job_sectorId,fld_job_sectorName,fld_job_sectorURL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It would really help if you posted some sample data in DML form and the DDL of the tables
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Location
    Belfast
    Posts
    16
    Perfect! Lovely jubbly. Thanks a million.

    @Brett - I'll remember that for the next time.
    ---------------------------------------
    Make something idiot proof- and someone will make a better idiot
    ----------------------------------------

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by paulanthony View Post
    Perfect! Lovely jubbly. Thanks a million.
    you're welcome

    in return, please tell me that you understood the change that i made, and why it works

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

Tags for this Thread

Posting Permissions

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