that IF is a nice little trick, wouldn’t work exactly like that in any other database except mysql, but could be made to work by using CASE instead of IF and MIN on the display_name even though there would be only one
if you don’t understand that remark, don’t worry
anyhow, i wanted to point out something else more important that you should be aware of
a LEFT OUTER JOIN is used whenever you want to return all rows of the left table with or without matching rows from the right table
so let’s look at this –
FROM jobs
LEFT OUTER
JOIN companies
ON companies.id = jobs.company_id
this says you expect there might be some rows in the jobs table that don’t have a matching row in the companies table
in other words, you want to include jobs for companies that don’t exist
is that realistic?
i didn’t think so
also, here’s another point – suppose that there actually are some jobs which don’t have a matching company, and you do want a LEFT OUTER JOIN
as you know, in a LEFT OUTER JOIN any rows in the result set for unmatched jobs will have all the columns in the result row that come from the companies table set to NULL
but then the WHERE clause takes over, and completely sabotages the “outerness” of the join by requiring that the company status have a non-NULL value
if you do really want to include jobs without companies in the result, then you need to move the WHERE condition into the ON clause of the join
but i suspect that what you really want is an inner join
finally, always use indentation in your SQL, it makes it a lot easier to read…
SELECT jobs.id
, jobs.job_title
, jobs.job_url
, companies.company_name
, companies.company_url
, locs.display_name
FROM jobs
INNER
JOIN companies
ON companies.id = jobs.company_id
AND companies.status = 'Active'
INNER
JOIN ( SELECT job_id
, CASE WHEN COUNT(*) > 1
THEN 'More than 1'
ELSE MIN(display_name)
END AS display_name
FROM jobs_locations
GROUP
BY job_id ) AS locs
ON locs.job_id = jobs.id
ORDER
BY jobs.date_posted DESC