SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select statement guidance sought...

    hi there, im trying to what i consider to be a complex select statemnent and iim not sure where to get started

    I have 2 tables, one with user details and one with jobs they have posted. ( im being vague atm as id like to have a go at writing it myself)

    What i want to do in lehmans terms is

    Select name from user where (job not posted in last 30 days)

    the job table contains the user id of the poster so i assume its fairly straight forward - can anyone point me in the right direction please

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, here's the solution in general terms

    set up a LEFT OUTER JOIN from the users table to the jobs table

    in the ON clause of the join, specify your search criteria on the jobs table, i.e. job was posted in last 30 days

    then add a WHERE clause to check for IS NULL in the joined jobs row, which indicates no match was found
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks r937!

    I hope you dont mind me asking more questions.

    Can you just elaborate on the last statement please about the null records, i assume i need to make sure that i dont picke the results that are null.

    so far i have...

    Code:
    SELECT job_employers.emp_company_name
    FROM job_employers
    LEFT OUTER JOIN job_jobs
    ON job_jobs.job_date_added < DATE_SUB(NOW(), INTERVAL 30 DAY);
    .....
    am i on the right track?

    thanks again

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    almost there. you also need to specify the relationship between the employers table and the jobs table in your ON clause, then add WHERE job_jobs.job_date_added IS NULL to the WHERE clause
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    excellent, i see now, i misinterepretted the solution!

    for anyone else who it might help i have this...

    Code:
    SELECT job_employers.emp_company_name
    FROM job_employers
    LEFT OUTER JOIN job_jobs
    ON (job_jobs.job_date_added < DATE_SUB(NOW(), INTERVAL 30 DAY)
    	AND job_jobs.user_id = job_employers.user_id)
       WHERE job_jobs.job_date_added IS NULL
    which seems to work ok.

    Thankyou again, its very satisfying getting through things without simply asking for the answer

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, not quite right, it's the other way around
    Code:
      FROM job_employers
    LEFT OUTER 
      JOIN job_jobs
        ON job_jobs.user_id = job_employers.user_id
       AND job_job.job_date_added > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
     WHERE job_jobs.user_id IS NULL
    by the way, you have ~way~ too many job job jobs in there


    .....

    the ON clause in the left outer join searches for something to find

    the WHERE clause filters out the rows where something was found, and returns only rows where something was not found
    Last edited by r937; Dec 3, 2008 at 13:23. Reason: Edit: messed up my left and my right :blush:
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks again Rudy,

    Why is that way different / better - i *seem* to be getting the same results - just so i can understand for the future

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you wanted "job not posted in last 30 days"

    so the technique involves searching for jobs within last 30 fays, and returning those employers where nothing was found

    your query searches for jobs posted earlier than 30 days, and returns those employers where nothing was found

    see the difference now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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