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
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
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.
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.