Select multiple records from sql database table in a master-detail scenario

Question

Hello, I have two tables in a master-detail relationship. The structure is more or less as follows: Master table:

MasterID, DetailID, date, ...
masterID1, detailID1, 2010/5/1, ....
masterID2, detailID1, 2008/6/14, ...
masterID3, detailID1, 2009/5/25, ...
masterID4, detailID2, 2008/7/24, ...
masterID5, detailID2, 2010/4/1, ...
masterID6, detailID4, 2008/9/16, ...

Details table:

DetailID, ...
detailID1, ...
detailID2, ...
detailID3, ...
detailID4, ...

I need to get all the records from the details table plus the LAST record from the master table (last by the date in the master table). Like this:

detailID1, masterID1, 2010/5/1, ....
detailID2, masterID5, 2010/4/1, ...
detailID3, null, null, ...
detailID4, masterID6, 2008/9/16, ...

I have no idea how to do this. Can anybody help me? Thanks a lot. Jan

Answer 1

Select ....
From Details As D
    Left Join   (
                Select M1.MasterId, M.DetailId, M.Date...
                From Master As M1
                Where MasterId  = (
                                    Select Max(M2.MasterId)
                                    From Master As M2
                                    Where M2.DetailId = M1.DetailId
                                    Group By M2.DetailId
                                    Having Max(M2.Date) = M1.Date
                                    )
                ) As M
        On M.DetailId = D.DetailId

What I'm doing here is dealing with ties. If you had two Master rows with the same DetailId and Date, I'm choosing the one with the highest MasterId.

Btw, this problem is significantly simpler if you have common table expressions (CTE). With a CTE you can do something like:

With LastMasterRows As
                (
                Select MasterId, DetailId, Date
                    , Row_Number() Over( Partition By DetailId Order By Date, MasterId ) As ItemRank
                From Master
                )
Select ...
From Details As D
    Left Join LastMasterRows As M
        On M.DetailId = D.DetailId
            And M.ItemRank = 1