zaggs — 2010-10-23T13:31:03-04:00 — #1
I have three tables:
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
JOIN companies ON companies.id = jobs.company_id
INNER JOIN applications ON applications.job_id = jobs.id
ORDER BY jobs.date_posted DESC
r937 — 2010-10-24T08:59:27-04:00 — #2
you'll need a LEFT OUTER JOIN for that
r937 — 2010-10-24T08:58:37-04:00 — #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
zaggs — 2010-10-24T08:52:23-04:00 — #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.
r937 — 2010-10-23T16:27:16-04:00 — #5
if there are no rows in the jobs table, that query returns 0 rows always
perhaps you could rephrase the problem?
scallioxtx — 2010-10-24T08:49:09-04:00 — #6
HAVING count( applications.id ) > 0 to your query.
zaggs — 2010-10-24T08:38:48-04:00 — #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
zaggs — 2010-10-24T08:40:29-04:00 — #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.