Robel Alemu Robel Alemu - 1 month ago 11
MySQL Question

mysql using limit in a left join not working properly

I have two tables looking like this

Patient (table 1)

id | name
------------
1 | robel
2 | dave


Patient_followup (table 2)

id | Patient_id | date_created
-----------------------
1 | ---- 1 -- | 01/01/2015
2 | -----1 -- | 01/07/2016


I want to display all the patients with their perspective latest followup data. so i tried using this query

Select * from patient
left join Patient_followup pf on pf.Patient_id = patient.id
order by pf.date_created
Limit 1


but this is giving me only the first patient robel. i tryed removing the limit and its giving me two records of robel and one record of dave because robel has two followup data. so what should i do to get only one record of each patient ?

Answer

Try this:

Select 
    * 
from 
    patient 
left join 
    (SELECT
        id as pf_id, 
        MAX(date_created) as latest_followup_date, 
        Patient_id
     FROM 
        Patient_followup 
     GROUP BY 
        Patient_id) as pf
ON pf.Patient_id = patient.id
Comments