MYSQL - Join most recent matching record from one table to another

Question

Hi,

I have two tables that look like this:

Table: cases

id
name
status
case_no

Table: notes

id
case_id
note_date
notes

I'd like to be able to create a query that grabs the data from the cases table and only the most recent entry from the notes table for each row in the cases table. So far I'm having no luck at all.

Any pointers would be greatly appreciated

Answer 1

This will return only the cases with notes attached:

SELECT c.*,
       x.*
  FROM CASES c
  JOIN NOTES x ON x.case_id = c.case_id
  JOIN (SELECT n.case_id,
               MAX(n.note_date) AS max_note_date
          FROM NOTES n
      GROUP BY n.case_id) y ON y.case_id = x.case_id
                           AND y.max_note_date = x.note_date

If you want all cases, regardless if they have a note attached:

   SELECT c.*,
          x.*
     FROM CASES c
LEFT JOIN NOTES x ON x.case_id = c.case_id
     JOIN (SELECT n.case_id,
                  MAX(n.note_date) AS max_note_date
             FROM NOTES n
         GROUP BY n.case_id) y ON y.case_id = x.case_id
                              AND y.max_note_date = x.note_date

Answer 2

    SELECT *
      FROM cases c
INNER JOIN notes n ON n.case_id = c.id
                  AND n.id = (SELECT MAX(id)
                                FROM notes
                               WHERE case_id = c.id)

Also it is a common practice to keep the pointer to the last note id directly in cases table and support it with trigger