Strategy for a query
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?
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.
ASKER
Indexes i will create for performance
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
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_pa
Left join surgery c on a.column=c.reference_to_pa
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.