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

Question

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

It would really help to know the DBMS you're using. MySql? MS Sql Server? Oracle? PostGre? — ErikE
– ErikE, Commented Feb 23, 2012 at 1:14
I don't think it's relevant because the solutions all seem to derive the right results, but it seems that you've mixed up the ObjectID in your expected results. 1 never had a status of fixed and 2 never had a status of sticky. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 23, 2012 at 2:07

ErikE · Accepted Answer · 2012-02-23 01:16:29Z

3

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

answered Feb 23, 2012 at 1:16

ErikE

50.7k23 gold badges157 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

General Grey Over a year ago

I am impressed. Works like a charm right out of the box. Thank you very much I have been fighting with this for days. I think I understand the syntax but I am going to play with it for a bit.

ErikE Over a year ago

@K'Leg If you have a huge number of objects with only a few statuses, then using a Row_Number() solution may perform better. The opposite would be fewer objects with a huge number of statuses each. It is possible I have this backwards (gosh, I always seem to forget this).

Aaron Bertrand · Accepted Answer · 2012-02-23 01:43:13Z

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.

Hogan · Accepted Answer · 2012-02-23 01:44:53Z

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

I like this less because you have to access the ObjectStatus table twice. YMMV.
@AaronBertrand - I agree that your example is probably the most performant here. My goal was to make it as clear as possible.
This also has the flaw that it assumes maxDate to be unique per object. That may not be true. The problem with this query, then (in my mind) is someone seeing it who hasn't been given this caveat, and wastes time trying to use it (or worse, uses it without realizing for some time the duplication problem).

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Related