Parent/Child/Child SELECT newest records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Parent/Child/Child SELECT newest records

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-04-08 : 09:48:27

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
Aged Yak Warrior

545 Posts

Posted - 2014-04-08 : 10:02:01

Outer Apply
( select top 1 records
from ChildTable
where ParentTable.id=ChildTable.id
order by date desc)

sabinWeb MCP

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-04-08 : 14:16:18

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

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-04-08 : 20:04:29

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources