SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Parent/Child/Child SELECT newest records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikeallenbrown
Starting Member

USA
47 Posts

Posted - 04/08/2014 :  09:48:27  Show Profile  Reply with Quote
I have a parent table that links to several child tables through two numeric fields. I want to select everything from the parent table and everything from the child table but only the last newest row entered in the child tables(where the linking fields are equal).

I know SQL enough to write a query to get everything from all my tables but I am unsure how to get only the newest rows where linked from the child tables.

Mike Brown

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 04/08/2014 :  10:02:01  Show Profile  Reply with Quote
Outer Apply
( select top 1 records
from ChildTable
where ParentTable.id=ChildTable.id
order by date desc)





sabinWeb MCP
Go to Top of Page

mikeallenbrown
Starting Member

USA
47 Posts

Posted - 04/08/2014 :  14:16:18  Show Profile  Reply with Quote
Below is my query. Patient is the PARENT table and PATIENTSTATUS is the child. I'm unsure how to work in your script into my query. I tried but I keep getting syntax errors.


SELECT Patient.MRNum, Patient.ClientID, Patient.Compined, Patient.PrimaryDiag, PatientStatus.Status, PatientStatus.Date, patientStatus.DischargedDate
FROM Patient
INNER JOIN PatientStatus ON Patient.MRNum=PatientStatus.MRNum2 AND Patient.ClientID=PatientStatus.Clientid2
WHERE (PatientStatus.Status NOT IN ('Pending', 'Non-Admit') ) AND Patient.Record_delete <> 1
ORDER BY Patient.Compined

Mike Brown
Go to Top of Page

mikeallenbrown
Starting Member

USA
47 Posts

Posted - 04/08/2014 :  20:04:29  Show Profile  Reply with Quote
I *think* I need 'CROSS APPLY' instead of 'OUTER' but I am not entirely sure. here is the query I came up with (see below). Each patient record in PATIENT may have multiple status' in PATIENTSTATUS but I only want the newest status that was entered ... does this look right?

SELECT *
FROM Patient P
CROSS APPLY (
SELECT TOP 1 PERCENT *
FROM PatientStatus PS
WHERE P.MRNum=PS.MRNum2 AND P.ClientID=PS.Clientid2
) PS
WHERE (PS.Status NOT IN ('Pending', 'Non-Admit') ) AND P.Record_delete <> 1

Mike Brown
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000