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.
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
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