Join Two tables using the group by function

[phanikumar32]

Hi All,

I tried lots ways to solve my problem.But i am not getting my required output.

I am explaining my requirement in detail below.

I have two tables in my database.From those two tables i want to get the entire data(all rows) of first table with join condition.
Along with that i am using where condition to get the maximum value records of the second table.
This is the query i am using:

   select pd.pid,pd.fname,pd.phone,pf.facilityid
   from patient_data pd
   left join tbl_patientfacility pf 
   on pd.pid = pf.patientid
   where pf.id in (select max(id) from tbl_patientfacility group by patientid) 

By applying this i am getting the data which id's are coming through the where condition,those records only displaying.But i need the records which are left in the first table.

I am attaching the images.
Could any one give me the suggestion on how to get my output.

[Rabbit]

Join the facility table to your subquery to return just the max rows before outer joining to your patient table.