SQL JOIN Statement

Question

Lets say I have a table e.g

Request No. Type  Status  
---------------------------
1           New   Renewed 

and then another table

Action ID Request No  LastUpdated    
------------------------------------
1           1         06-10-2010
2           1         07-14-2010
3           1         09-30-2010

How can I join the second table with the first table but only get the latest record from the second table(e.g Last Updated DESC)

Answer 1

Using aggregates:

SELECT r.*, re.*
  FROM REQUESTS r
  JOIN REQUEST_EVENTS re ON re.request_no = r.request_no
  JOIN (SELECT t.request_no,
               MAX(t.lastupdated) AS latest
          FROM REQUEST_EVENTS t
      GROUP BY t.request_no) x ON x.request_no = re.request_no
                              AND x.latest = re.lastupdated

Using LEFT JOIN & NOT EXISTS:

SELECT r.*, re.*
  FROM REQUESTS r
  JOIN REQUEST_EVENTS re ON re.request_no = r.request_no
 WHERE  NOT EXISTS(SELECT NULL
                     FROM REQUEST_EVENTS re2
                    WHERE re2.request_no = r2.request_no
                      AND re2.LastUpdated > re.LastUpdated)

Answer 2

SELECT r.RequestNo, r.Type, r.Status, a.ActionID, MAX(a.LastUpdated) 
FROM Request r
INNER JOIN Action a ON r.RequestNo = a.RequestNo
GROUP BY r.RequestNo, r.Type, r.Status, a.ActionID

Answer 3

SELECT T1.RequestNo     ,
       T1.Type ,
       T1.Status,
       T2.ActionId      ,
       T2.LastUpdated
FROM   TABLE1 T1
       JOIN TABLE2 T2
       ON     T1.RequestNo = T2.RequestNo
WHERE  NOT EXISTS
       (SELECT *
       FROM    TABLE2 T2B
       WHERE   T2B.RequestNo   = T2.RequestNo
       AND     T2B.LastUpdated > T2.LastUpdated
       )

Answer 4

SELECT *
FROM REQUEST, ACTION
WHERE REQUEST.REQUESTNO = ACTION.REQUESTNO --Joining here
AND ACTION.LastUpdated = (SELECT MAX(LastUpdated) FROM ACTION WHERE REQUEST.REQUESTNO = ACTION.REQUESTNO);

A sub-query is used to get the last updated record's date and matches against itself to prevent the other records being joined.

Granted, depending on how precise the LastUpdated field is, it can have problems with two records being updated on the same date, but that is a problem encountered in any other implementation, so the precision would have to be increased or some other logic would have to be in place or another distinguishing characteristic to prevent multiple rows being returned.

Answer 5

We can use the operation Top 1 with ORDER BY clause. For instance, if your tables are RequestTable(ID,Type,Status) and ActionTable(ActionID,RequestID,LastUpdated), the query will be like this:

Select Top 1 rq.ID, rq.Status, at.ActionID
From RequestTable as rq
JOIN ActionTable  as at ON rq.ID = at.RequestID
Order by at.LastUpdated DESC