468,512 Members | 1,502 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,512 developers. It's quick & easy.

Join Two tables using the group by function

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:
Expand|Select|Wrap|Line Numbers
  1.    select pd.pid,pd.fname,pd.phone,pf.facilityid
  2.    from patient_data pd
  3.    left join tbl_patientfacility pf 
  4.    on pd.pid = pf.patientid
  5.    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.
Attached Images
File Type: jpg InPut.jpg (41.6 KB, 115 views)
File Type: png ExpectedOutput.png (3.5 KB, 79 views)
Mar 10 '15 #1
1 1236
12,513 Expert Mod 8TB
Join the facility table to your subquery to return just the max rows before outer joining to your patient table.
Mar 10 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Mark | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.