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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -