Tutorial :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)

Solution:1

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         )

Solution:2

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)

Solution:3

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.

Solution:4

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

Solution: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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com