title: MySQL aggregate functions with LEFT JOIN
body: <p>It's Friday afternoon<sup>*</sup> and my brain has stopped working. Normally it is me who's answering dumb SQL questions like this, sorry!</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am trying to get <em>all</em> jobs that meet the <code>WHERE jobs.</code>&hellip; criteria, and if they have one, the timestamp of the latest <code>type=complete</code> note associated with that job:</p>
  
  <pre><code>Job ID     Complete Date
  1          today
  2          NULL
  4          yesterday
  </code></pre>
  
  <p>Job 3 don't appear because it don't meet the <code>jobs.status</code> criteria. But what I actually get is:</p>
  
  <pre><code>Job ID     Complete Date
  1          today
  4          yesterday
  </code></pre>
  
  <p>Job 2 is missing, i.e. the JOIN is acting like an INNER JOIN.<br>
  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.</p>
  
  <p>Specifically, users can delete notes, so potentially a complete/closed job may not have a <code>type=complete</code> 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.</p>
  
  <p><sub>* somewhere in the east</sub></p>