By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Join Two tables using the group by function

P: 22
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, 66 views)
File Type: png ExpectedOutput.png (3.5 KB, 55 views)
Mar 10 '15 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
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.