We help IT Professionals succeed at work.

Strategy for a query

I would like a little help in forming a strategy and query to solve a problem i have.

I have a table called Patients that contains a list of patients.  (One row per patient)

There are three other tables called Population, Incidents and Surgery.  These three tables have a foreign key realtionship with the Patients table.

A patient does not have to have a row in any of the three tables, but can also have multiple rows.  Each row in these tables has a Registration Date which combined with the patient foreign key makes the row unique.

I need to create view over the three tables that shows the Patient and his/her LAST entry in each table.

What would be the best strategy for creating this view?  Should i create CTEs for each table that return the latest row for each patient.  An then join the CTEs with the Patients table? Is there a better way?
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

Use OUTER APPLYs, like so:


SELECT P.col1, P.col2, Pop.colA, Pop.colB, Inc.col1, ..., Sur.col1, ...
FROM Patients P
OUTER APPLY (
    SELECT TOP (1) P2.*
    FROM dbo.Population P2
    WHERE P2.Patient_Id = P.Patient_Id
    ORDER BY P2.[Registration Date] DESC
) AS Pop
OUTER APPLY (
    SELECT TOP (1) I.*
    FROM dbo.Incidents I
    WHERE I.Patient_Id = P.Patient_Id
    ORDER BY I.[Registration Date] DESC
) AS Inc
OUTER APPLY (
    SELECT TOP (1) S.*
    FROM dbo.Surgery S
    WHERE S.Patient_Id = P.Patient_Id
    ORDER BY S.[Registration Date] DESC
) AS Sur

Distinguished Expert 2019

Commented:
Given HIPAA, sample data not option.

Please post the create table directives for the three tables

Select a.*,b.*,c.* from patients a left join table population b on a.column=b.reference_to_patients
                 Left join surgery c on a.column=c.reference_to_patients

Where croteria.if any

The select needs to include the columns from the respective table of interest to you

The result without a restriction in patients and any available data from the other two.
If no entries exist, the columns in other tables will be null.
select * from patients
inner join ( select top 1 from TABLE_A where patient_id = patients.id order by date desc ) as a
inner join ( select top 1 from TABLE_B where patient_id = patients.id order by date desc ) as b
inner join ( select top 1 from TABLE_C where patient_id = patients.id order by date desc ) as c
where patients.id = WHATEVER

the above will create 3 temporary tables for the inner queries but should be fast given the fact only a single row will be fetched.

whatever your query strategy, having multi-column indexes on each of the 3 tables with ( patient_id,date ) should make it run instantly even for huge datasets.
soozhCEO

Author

Commented:
Thanks.  I will use the OUTER APPLY solution from Scott who managed to almost quess correctly the column names in my tables.

Indexes i will create for performance
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

There is one alternative.  This query might perform better than the other one, it might not.  It just depends on specific indexes and amounts of data.


SELECT P.col1, P.col2, Pop.colA, Pop.colB, Inc.col1 /*, ..., Sur.col1, ...*/
FROM Patients P
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Patient_Id
        ORDER BY [Registration Date] DESC) AS row_num
    FROM dbo.Population
) AS Pop ON Pop.Patient_Id = P.Patient_Id AND Pop.row_num = 1
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Patient_Id
        ORDER BY [Registration Date] DESC) AS row_num
    FROM dbo.Incidents
) AS Inc ON Inc.Patient_Id = P.Patient_Id AND Inc.row_num = 1
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Patient_Id
        ORDER BY [Registration Date] DESC) AS row_num
    FROM dbo.Surgery
) AS Sur ON Sur.Patient_Id = P.Patient_Id AND Sur.row_num = 1