Select MAX among multiple tables in MYSQL

Question

I am trying to list patients and their most recent primary insurance benefits. I have three tables involved,

patients (contains patient demographic data)
benefits (contains insurance benefits information)
rel_pat_ben (matches primary keys of patient table with corresponding key on benefits table)

I almost solved the problem with this:

SELECT p.patient_id, MAX(b.benefits_id), b.effective_date
FROM patients AS p
LEFT JOIN rel_pat_ben AS rpb USING(patient_id)
LEFT JOIN benefits AS b ON (rpb.benefits_id=b.benfits_id AND b.order='primary')
GROUP BY patient_id

Using MAX seemed to work as the id field is auto-incremented, but I realized my problem when I found cases where a record that has a lower id was edited with newer information. So really, I need to check the 'effective_date' field.

Is there a way to return just one record per patient that contains only the most recent insurance benefits?

Answer 1

This will select the maximum effective_date, if it exists, for every patient:

SELECT p.patient_id, MAX(b.effective_date)
FROM
  patients AS p INNER JOIN rel_pat_ben AS rpb USING(patient_id)
  INNER JOIN benefits AS b ON (rpb.benefits_id=b.benfits_id AND b.order='primary')
GROUP BY
  p.patient_id

and this will select last benefit for every patient:

SELECT p.patient_id, b.*
FROM
  patients AS p INNER JOIN rel_pat_ben AS rpb USING(patient_id)
  INNER JOIN benefits AS b ON (rpb.benefits_id=b.benfits_id AND b.order='primary')
WHERE
  (p.patient_id, b.effective_date) IN (
    SELECT p.patient_id, MAX(b.effective_date)
    FROM
      patients AS p INNER JOIN rel_pat_ben AS rpb USING(patient_id)
      INNER JOIN benefits AS b ON (rpb.benefits_id=b.benfits_id AND b.order='primary')
    GROUP BY
      p.patient_id
  )

I'm using INNER JOINS so if there are no benefits for a patient, it won't be returned.