Join Record With Most Recent Record On Second Table

I have 2 tables Delivery -------- deliveryid int (PK) description long varchar DeliveryHistory --------------- historyid int delievryid int statusid int recordtime timestamp

Solution 1:

Your where clause is resulting in all null values being excluded. Try

where  h.RecordTime isnullOR
       h.recordtime =
       ( SELECTMAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

Solution 2:

select d.deliveryid,d.description, h.statusid from delivery d
leftouterjoin  Deliveryhistory h on d.deliveryid = h.deliveryid
where  (h.recordtime =
   ( SELECTMAX(recordtime)
       FROM Deliveryhistory
      WHERE deliveryid = d.deliveryid)
  or h.deliveryid =null)

Solution 3:

The existing answers are all it takes but if you'd like to do this without using a WHERE clause you can use following construct.

SELECT  d.deliveryid
        ,d.description
        , dh.statusid
FROM    Delivery d 
        LEFTOUTERJOIN (
          SELECT deliveryid, MAX(recordtime) AS recordtime
          FROM   DeliveryHistory
          GROUPBY
                 deliveryid
        ) dhm ON dhm.deliveryid = d.deliveryid                 
        LEFTOUTERJOIN DeliveryHistory dh ON dh.deliveryid = dhm.deliveryid 
                                              AND dh.recordtime = dhm.recordtime

Solution 4:

CTE to yield the maxrow (IFF the implementation supports CTEs ;-) plus simple left join with the CTE.

WITHlastAS (
        SELECT*FROM Deliveryhistory dh
        WHERENOTEXISTS (
                SELECT*FROM Deliveryhistory nx
                WHERE nx.deliveryid = dh.deliveryid
                AND nx.recordtime > dh.recordtime -- no one is bigger: dh must be the max
                )
        )
SELECT d.deliveryid, d.description, l.statusid 
FROM delivery d
LEFTJOINlast l ON d.deliveryid = l.deliveryid
        ;