Fetching records from multiple tables
I have three tables in database as given below. I have to get records from these three tables such that the RF_date should be between the startdate and end date that I would be supplying to the query and then page_id should be equal to pagetype that I will supply to the query and one last is that Assigned_To. so suppose I sent " 10/10/2003 12:00:00 AM" as start date and 10/10/2015 12:00:00 AM as end date and pagetype=1 then probably I will get all three records from the table tblFrontUsers but for each this record I have multiple entires in second table i.e "tblPatient", so I need to get latest "Assigned_To" for each user. for eg: for 115,latest there is "5" and for 116, the latest is "1" and then this 5 and 1 have it's names stored in the last table i.e tblUsers. I am trying with following query but I am finding difficult to get the desired results. Please help.
select tblFrontUsers .Name, tblFrontUsers .ID, tblFrontUsers.page_Id, tblPatient.Patient_ID , tblUsers.Name tblPatient.Assigned_To from tblFrontUsers left join tblPatient on tblPatient .ID=tblFrontUsers .ID left join tblUsers on tblUsers.Id = tblPatient .Assigned_To where tblFrontUsers .Rf_Date >= '12/17/2003 12:00:00 AM' and tblFrontUsers .Rf_Date<='3/21/2014 12:00:00 AM' and tblFrontUsers.page_Id=1 and there can be search column with tblPatient.Assigned_To also. If it is present we need to get records only with this otherwise all. tblFrontUsers ID Name Rf_Date page_Id 115 Sandy 10/10/2013 12:00:00 AM 1 116 Javer 10/25/2013 12:00:00 AM 1 117 Felipe 10/22/2013 12:00:00 AM 1 tblPatient Patient_ID ID Assigned_To 1 115 2 2 115 3 3 115 5 4 116 5 5 116 3 6 116 1 tblUsers Id Name 1 Max 2 Sam 3 Harry 4 mary 5 maria
select * from tblFrontUsers Fo left join (select max(Patient_ID) as Patient_ID,ID from tblPatient group by ID)P on P.ID=Fo.ID inner join tblUsers U on U.Id=(select Assigned_To from tblPatient P1 where P1.Patient_ID=P.Patient_ID) where (FO.Rf_Date between '12/17/2003 12:00:00 AM' and '3/21/2014 12:00:00 AM' and FO.page_Id=1) order by Fo.[ID]
select * from tblFrontUsers Fo left join (select max(Patient_ID) as Patient_ID,ID from tblPatient group by ID)P on P.ID=Fo.ID left join tblPatient P1 on P1.Patient_ID=P.Patient_ID left join tblUsers U on U.Id=P1.Assigned_To where (FO.Rf_Date between '12/17/2003 12:00:00 AM' and '3/21/2014 12:00:00 AM' and FO.page_Id=1) --and p1.Assigned_To='2' order by Fo.[ID]