MySQL aggregate functions with LEFT JOIN

Question

It's Friday afternoon^* and my brain has stopped working. Normally it is me who's answering dumb SQL questions like this, sorry!

I am trying to get one table, along with the highest value of a column of another table by LEFT JOINing the latter to the former.

SELECT
  jobs.*,
  MAX(notes.`timestamp`) AS complete_date
FROM jobs
LEFT JOIN notes ON (jobs.id=notes.job_id)
WHERE (jobs.status="complete" OR jobs.status="closed")
  AND (notes.type="complete" OR notes.type IS NULL)
GROUP BY jobs.id
ORDER BY complete_date ASC

I am trying to get all jobs that meet the WHERE jobs.… criteria, and if they have one, the timestamp of the latest type=complete note associated with that job:

Job ID     Complete Date
1          today
2          NULL
4          yesterday

Job 3 don't appear because it don't meet the jobs.status criteria. But what I actually get is:

Job ID     Complete Date
1          today
4          yesterday

Job 2 is missing, i.e. the JOIN is acting like an INNER JOIN.
I am sure it's just me having a brain-dead moment, but I can't see why my LEFT (OUTER) JOIN is not giving me all jobs regardless of the value of the note.

Specifically, users can delete notes, so potentially a complete/closed job may not have a type=complete note on it (the notes are entered when the status is changed), I am trying to catch the case when a user closes a job, adds a note, then deletes the note.

_{* somewhere in the east}

Taryn Taryn · Accepted Answer · 2013-08-08T15:16:11

Since you have the filter for the notes table in the WHERE clause the JOIN is acting like an INNER JOIN, move it to the JOIN condition:

SELECT
  jobs.*,
  MAX(notes.`timestamp`) AS complete_date
FROM jobs
LEFT JOIN notes 
  ON (jobs.id=notes.job_id)
  AND (notes.type="complete" OR notes.type IS NULL)
WHERE (jobs.status="complete" OR jobs.status="closed")
GROUP BY jobs.id
ORDER BY complete_date ASC;

This could also be done using a subquery, so you apply the notes filter inside the subquery:

SELECT
  jobs.*,
  n.complete_date
FROM jobs
LEFT JOIN
(
    select job_id, MAX(`timestamp`) AS complete_date
    from notes 
    where (type="complete" OR type IS NULL)
    group by job_id
) n
  ON (jobs.id=n.job_id)
WHERE (jobs.status="complete" OR jobs.status="closed")
ORDER BY complete_date ASC

MySQL aggregate functions with LEFT JOIN

1 Answers