SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INNER JOIN question

    Hi Guys!

    I have three tables:
    jobs
    companies
    applications

    I need to join companies to jobs. I also need to join applications to jobs, but only if a job exists. The below sql works, but it returns a row even if there are no rows in the jobs table. How can I make it so that if there are no jobs then nothing is returned?

    PHP Code:
    SELECT jobs. *, companies.company_namecountapplications.id ) AS applications
    FROM jobs
    JOIN companies ON companies
    .id jobs.company_id
    INNER JOIN applications ON applications
    .job_id jobs.id
    ORDER BY jobs
    .date_posted DESC 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if there are no rows in the jobs table, that query returns 0 rows always

    perhaps you could rephrase the problem?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if there are no rows in the jobs table, that query returns 0 rows always

    perhaps you could rephrase the problem?
    This is not true. The query returns 1 row like this when there is nothing in the jobs table:

    Code:
    id     job_title     location     job_salary     job_ote     job_perks     company_id     consultant_id     job_type     industry     job_description     date_posted     company_name     applications
    NULL     NULL     NULL     NULL     NULL     NULL     NULL     NULL     NULL     NULL     NULL     NULL     NULL     0

  4. #4
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it's because of the count on the applications table. How do I get around that problem? I only want to return an applications count if there is a job.

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,904
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Try adding HAVING count( applications.id ) > 0 to your query.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Try adding HAVING count( applications.id ) > 0 to your query.
    Sure but that will only return rows that have applications, right? I still want to show rows that have 0 applications, but dont want to show rows when there are 0 jobs.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    oh, i completely missed the COUNT in the SELECT clause

    know why? because there was no GROUP BY clause (and there should be, since there are also non-aggregates in the SELECT clause -- i'm not entirely sure why mysql allows this syntax error to actually be executed)

    since the intent of the query seems to be to count applications per job, add GROUP BY jobs.id and then i'm certain you will get no rows back when the jobs table is empty

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Zaggs View Post
    I still want to show rows that have 0 applications
    you'll need a LEFT OUTER JOIN for that

    r937.com | rudy.ca | 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
  •