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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikeallenbrown
Yak Posting Veteran

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

Romania
545 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
Yak Posting Veteran

USA
72 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
Yak Posting Veteran

USA
72 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  
 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