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

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

---

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

---

    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

---

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