INNER JOIN question

Databases
1

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?


SELECT jobs. *, companies.company_name, count( applications.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

you’ll need a LEFT OUTER JOIN for that

:slight_smile:

3

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

:slight_smile:

4

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.

5

if there are no rows in the jobs table, that query returns 0 rows always

perhaps you could rephrase the problem?

6

Try adding HAVING count( applications.id ) > 0 to your query.

7

This is not true. The query returns 1 row like this when there is nothing in the jobs table:

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
8

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.