CODEWITHSUNDEEP
sqlsql-server-2008
General Grey
General Grey

Reputation: 3688

SQL retrieve data from two table based on the ID(Key) of the first table

I imagine this has been done many time before but I can't for the life of me figure it out and I am tired of trying work arounds.

I have two tables.
One is an Object having ObjectID(key), Name, Type, Location Two is ObjectStatus having StatusID(key),ObjectID,Status,DateChanged,UserWhoChangedStatus

What I want to do is return all Objects and the ObjectStatus that was entered last

Table 1 Object

ObjectID      Name          Type      Location
   1         Blue Ball      Ball       ToyBox
   2         Red Ball       Ball       ToyBox

Table 2 (ObjectStatus)

StatusID    ObjectID    Status    DateChanged    UserWhoChangedStatus
   1          2         Broken    2012-01-25       56481
   2          2         Fixed     2012-01-30       98526
   3          1         Bouncy    2012-01-05       85245
   4          1         Sticky    2012-02-10       56481

I would want to get returned

ObjectID Name      Type Location StatusID Status DateChanged UserWhoChangedStatus
   1     Blue Ball Ball ToyBox       4    Sticky 2012-02-10  56481
   2     RedBall   Ball ToyBox       2    Fixed  2012-01-30  98526

Which is all Objects and the ObjectStatus that was last entered

Upvotes: 3

Views: 906

Answers (3)

Hogan
Hogan

Reputation: 70523

There are a number of way to do this but I like the the following way because it seems most intuitive -- it uses a CTE and left joins which makes it simpler if the query gets more complicated:

WITH maxDate AS
(
    SELECT objectID, MAX(DateChanged) AS maxDate
    FROM ObjectStatus
    GROUP BY objectID
)
SELECT O.*, OS.*
FROM Object O
LEFT JOIN maxDate ON maxDate.objectID = O.ObjectID
LEFT JOIN ObjectStatus ON OS.ObjectID = O.ObjectID 
                       AND OS.DateChanged = maxDate.maxDate

Upvotes: 0

anon
anon

Reputation:

Just to provide an alternate solution so future readers can compare performance and pick the appropriate method in their case.

;WITH LastChange AS
(
  SELECT 
    ObjectID, Status, DateChanged, UserWhoChangedStatus,
      rn = ROW_NUMBER() OVER (PARTITION BY ObjectID ORDER BY DateChanged DESC)
    FROM dbo.ObjectStatus
)
SELECT 
  o.ObjectID, o.Name, o.Type, o.Location,
  l.StatusID, l.Status, l.DateChanged, l.UserWhoChangedStatus
FROM dbo.Object AS o
LEFT OUTER JOIN LastChange AS l
ON o.ObjectID = l.ObjectID
AND l.rn = 1;

You can change the LEFT OUTER JOIN to INNER JOIN if you somehow know that the status table will always have at least one row for every ObjectID, or if you don't want to return objects that don't have a row in status.

Upvotes: 1

ErikE
ErikE

Reputation: 50201

Since you didn't say the DBMS I will assume Ms Sql Server.

SELECT
   O.*,
   S.*
FROM
   dbo.Object O
   OUTER APPLY (
      SELECT TOP 1 *
      FROM dbo.ObjectStatus S
      WHERE O.ObjectID = S.ObjectID
      ORDER BY DateChanged DESC
   ) S

Upvotes: 3

Related Questions