Querying tables with multiple records tracked by date

Question

I have multiple tables 7 or more. There is a master table and all other are child tables (FK to master table primary column id). All the tables have multiple records for a given id, including master table and they are tracked by date (effective date). And most recent date record is considered active record in each table.

Is there a fast and better way to join them and return most recent records from each table? Right now i select max from each table and join on that to return recent record from different tables.

Answer 1

Maybe creating a view for all table that return TOP 1 of each table ORDER BY effective date.

Then in you main query do the JOIN on all those views.

Answer 2

you can write something like this:

select * from masterTable m
cross apply (select top 1 null as dummy from masterTable m2 where m2.id= m.id order by m2.effectDate desc) mm1
cross apply (select top 1 * from detailTable1 d1 where d1.masterId = m.id order by d1.effectDate desc) dd1
cross apply (select top 1 * from detailTable2 d2 where d2.masterId = m.id order by d2.effectDate desc) dd2
cross apply (select top 1 * from detailTable2 d2 where d3.masterId = m.id order by d3.effectDate desc) dd2

I do not have your tables, so I cannot test this, hope this helps.

Answer 3

Being that you have an inkex on (id,effdt) I think you have already described the most efficient way of querying your schema. At least, the method you describe is the way I do it...

SELECT
  *
FROM
  master
INNER JOIN
  child1
    ON child1.id = master.id
INNER JOIN
  child2
    ON child2.id = master.id
WHERE
      master.effdt = (SELECT TOP 1 effdt FROM master AS lookup WHERE id = master.id)
  AND child1.effdt = (SELECT TOP 1 effdt FROM child1 AS lookup WHERE id = child1.id)
  AND child2.effdt = (SELECT TOP 1 effdt FROM child2 AS lookup WHERE id = child2.id)

Resolving the different sub-queries (I use TOP 1, MAX() also works) then becomes an index lookup.

There is an alternative, though I don't recall there being any significant performance benefit...

SELECT
  *
FROM
  (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY effdt), * FROM master) AS master
INNER JOIN
  (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY effdt), * FROM child1) AS child1
    ON child1.id = master.id
INNER JOIN
  (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY effdt), * FROM child2) AS child2
    ON child2.id = master.id
WHERE
      master.seq_id = 1
  AND child1.seq_id = 1
  AND child2.seq_id = 1

Additionally, there is the CROSS APPLY option as mentioned by Maziar...

SELECT
  *
FROM
  master
CROSS APPLY
  (SELECT TOP 1 * FROM child1 WHERE id = master.id ORDER BY effdt DESC) AS child1
CROSS APPLY
  (SELECT TOP 1 * FROM child2 WHERE id = master.id ORDER BY effdt DESC) AS child2
WHERE
  master.effdt = (SELECT TOP 1 effdt FROM master AS lookup WHERE id = master.id)