CODEWITHSUNDEEP
sqlsql-server
aicoder
aicoder

Reputation: 907

SQL Query with joins but only want latest record from joined table

I have a database with a table for Engines (EngineId, Make, Model, SerialNumber, etc.) and a table with Test values on the engines (TestId, EngineId, Value1, Value2, etc).

I want to select all the records from the Engines table along with the latest test values (there may be mutliple test records but I only want the latest). It seems simple but for some reason I can't quite get the SQL syntax.

I can get started with something like the following but then I'm stuck.

SELECT e.*, t.Value1, t.Value2
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID
WHERE ev.EventType = 'TEST'

I can get close with the following but it only returns records with test data. I want to return engine records without test data:

SELECT e.*, t.Nox, t.CO
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID
left join EventType et on ev.EventTypeId = et.EventTypeID
WHERE Upper(et.Name) = 'TEST'
  AND ev.EventDate= ( SELECT MAX(EventDate) 
                        FROM Event ev2
                        left join EventType et2 on ev2.EventTypeID = et2.EventTypeID
                        WHERE ev2.EngineID = e.EngineID AND 
                        Upper(et2.Name) = 'TEST')

I can get close with the following but I still need to join to Test table somehow:

SELECT DISTINCT e.*, om.maxdate
FROM Engine e
LEFT JOIN (
  select ev2.EngineID, max(EventDate) as maxdate
  from Event ev2
  LEFT JOIN EventType et on ev2.EventTypeID = et.EventTypeID
  WHERE Upper(et.Name) = 'TEST'
  group by EngineID
) om on e.EngineID = om.EngineID
--LEFT JOIN Test t on om.EventID = t.EventID

I can do a stored procedure if that helps.

You asked for some sample data so here goes. I want to return all the engine records and along with that the latest test values.

Engine

EngineId    Make    Model   SerialNumber
    1   Waukesha    5794    C-62124
    2   Waukesha    5800    F-199374
    3   NULL        NULL    B-200305
    4   Waukesha    6000    D-999777
    5   Waukesha    6100    E-877324
    6   Briggs & Stratton   LW300   SL-122375
    7   Briggs & Stratton   LW350   SL-375200
    8   Briggs & Stratton   GS250   LS-988755
    9   Briggs & Stratton   5794    SN12345

Test

TestID  EventID EngineID    TestDate    Description Value1  Value2
1   7   1   03/01/17 12:00 AM   Testing 30  50
9   8   1   01/01/17 12:00 AM   Testing 20  20
17  11  1   12/01/16 12:00 AM   Testing 16  15

Upvotes: 2

Views: 90

Answers (5)

pcofre
pcofre

Reputation: 4066

You can use rank() function to get only the last test per engine.

;with Tcte as (
SELECT e.*
, t.Value1
, t.Value2
, rank() over (partition by e.EngineID order by t.TestDate desc) r 
FROM Engine e 
left join Event ev on e.EngineID = ev.EngineID 
left join Test t on ev.EventID = t.EventID WHERE ev.EventType = 'TEST')
SELECT * FROM Tcte WHERE r=1

Upvotes: 0

Eid Morsy
Eid Morsy

Reputation: 966

Try this

 ;with testValues as
 ( select ev.EngineID, ev.EventId ,t.TestId,t.Value1, t.Value2 
   ,row_number()  over(partition by ev.EventId order by ev.EventDate desc)rowNumber
   from Event ev
   left join Test t on ev.EventID = t.EventID
   WHERE ev.EventType = 'TEST'
 )

SELECT e.*, v.Value1, v.Value2
FROM Engine e
left join testValues v on e.EngineID = v.EngineID and v.rowNumber=1

Or this

;with testData as
(
 SELECT e.*, t.Value1, t.Value2
 ,row_number()  over(partition by ev.EventId order by ev.EventDate desc)rowNumber
 FROM Engine e
 left join Event ev on e.EngineID = ev.EngineID and ev.EventType = 'TEST'
 left join Test t on ev.EventID = t.EventID
) 
select * from testData where rowNumber=1

Upvotes: 0

miroxlav
miroxlav

Reputation: 12194

If you mean latest TEST event (based on EventDate) for given engine, then it is:

SELECT e.*, t.Value1, t.Value2
  FROM Engine e
    LEFT JOIN Event ev
      ON e.EngineID = ev.EngineID
          AND ev.EventType = 'TEST'
          AND ev.EventDate = (SELECT MAX(ev2.EventDate)
                              FROM Event ev2
                              WHERE ev2.EngineID = e.EngineID
                                  AND ev2.EventType = 'TEST')
    LEFT JOIN Test t
      ON ev.EventID = t.EventID

Please note that if the latest event has more test records, all of them will be returned, because all of them share the same latest EventDate. But this seems to be correct because you did not made distinction between multiple Test records assigned to the same Event, so they are equal.

Upvotes: 0

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

When you say that you want the latest test-values, that implies that you'll have some kind of way of identifying the latest test-values. This can be done by retrieving the latest eventdate.

You will have to use a subquery with the query. 

SELECT e.*, t.Value1, t.Value2
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID
WHERE ev.EventType = 'TEST'
  AND ev.EventDate= ( SELECT MAX(EventDate) 
                        FROM Event
                        WHERE Event.EventID = t.EventId
                          AND Event.EngineID = ev.EngineId )

This should give you the latest test-results for an event on an engine

Upvotes: 0

perfo
perfo

Reputation: 412

you can use the 

  SELECT TOP 1 e. *,t.Value1,t.Value2

to get the top record and to make sure the top is the latest, order the table with 

  ORDER BY column1, column2, ... ASC|DESC

Hope this helps....

Upvotes: 1

Related Questions