Left outer join problem

Databases
1

Hi Guys!

I am trying to get the number of applications from the “applications” table in the following sql query. However, if there are no jobs matching the search criteria, there is a blank row returned with all NULL values. It should just return no rows.

How can I fix this?

SQL Query:


SELECT jobs.job_title, companies.company_name, companies.company_url, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.location, jobs.date_posted, industries.industry, job_types.job_type, count( applications.id ) AS applications
FROM jobs
LEFT JOIN companies ON companies.id = jobs.company_id
LEFT JOIN industries ON industries.id = jobs.industry
LEFT JOIN job_types ON job_types.id = jobs.job_type
LEFT OUTER JOIN applications ON applications.job_id = jobs.id
WHERE jobs.status = 'on'
AND jobs.job_title LIKE '%test%'
ORDER BY date_posted DESC

Result:


job_title 	company_name 	company_url 	job_url 	job_salary 	job_ote 	job_perks 	job_description 	location 	date_posted 	industry 	job_type 	applications
NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	0

Thanks.

2

the “all NULLs” row is not produced by mysql

please run your query outside of php to confirm :slight_smile:

by the way, since you are using COUNT(), you definitely need a GROUP BY clause

also, those first three LEFT JOINs should be INNER JOINs – only the last one should stay as a LEFT OUTER JOIN