Robel Alemu Robel Alemu - 1 year ago 75
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 =
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 Source

Try this:

left join 
        id as pf_id, 
        MAX(date_created) as latest_followup_date, 
     GROUP BY 
        Patient_id) as pf
ON pf.Patient_id =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download