Avatar of soozh
soozh
Flag for Sweden asked on

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?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
arnold

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

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

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Pletcher

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