CODEWITHSUNDEEP
sqljoin
user1247395
user1247395

Reputation: 419

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

WHat I am trying to do is a left outer join to bring back all records from table Delivery with only the most recent entry in DeliveryHistory for each delivery. However if there are no entries in the DeliveryHistory for the delivery I would like a null value

I have done this:

select d.deliveryid,d.description, h.statusid from delivery d
left outer join  Deliveryhistory h on d.deliveryid = h.deliveryid
where  h.recordtime =
       ( SELECT MAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

But it only returns the rows that have an entry in DeliveryHistory.

Upvotes: 2

Views: 157

Answers (4)

wildplasser
wildplasser

Reputation: 44240

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

WITH last AS (
        SELECT * FROM Deliveryhistory dh
        WHERE NOT EXISTS (
                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
LEFT JOIN last l ON d.deliveryid = l.deliveryid
        ;

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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 
        LEFT OUTER JOIN (
          SELECT deliveryid, MAX(recordtime) AS recordtime
          FROM   DeliveryHistory
          GROUP BY
                 deliveryid
        ) dhm ON dhm.deliveryid = d.deliveryid                 
        LEFT OUTER JOIN DeliveryHistory dh ON dh.deliveryid = dhm.deliveryid 
                                              AND dh.recordtime = dhm.recordtime

Upvotes: 0

YetAnotherUser
YetAnotherUser

Reputation: 9346

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

where  h.RecordTime is null OR
       h.recordtime =
       ( SELECT MAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

Upvotes: 2

Paul Michaels
Paul Michaels

Reputation: 16685

select d.deliveryid,d.description, h.statusid from delivery d
left outer join  Deliveryhistory h on d.deliveryid = h.deliveryid
where  (h.recordtime =
   ( SELECT MAX(recordtime)
       FROM Deliveryhistory
      WHERE deliveryid = d.deliveryid)
  or h.deliveryid = null)

Upvotes: 1

Related Questions