MYSQL - Join most recent matching record from one

Question

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

I have been having same problem recently and this forum has helped me a lot but I found the OMG Ponies answer isn't complete. It works for those cases with notes but it doesn't for cases without notes which is my issue. My answer is similar but instead joining the group query with notes, I join it with cases.

It would be:

SELECT c.*, x.*
FROM CASES c
    LEFT 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 = c.case_id
    LEFT JOIN NOTES x ON x.case_id = c.case_id AND x.note_date=y.max_note_date

It's also valid to get just cases with notes removing one left keyword or both

Answer 2

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 3

    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