CODEWITHSUNDEEP
sqlsql-servert-sql
forgot
forgot

Reputation: 2185

Find First Match

I have two tables that look roughly like this (only including relevant info):

------------ Master --------------
ID | Master_Number | First_Arrival

---------- Vehicle ------------
ID | Master_ID | Name | Arrived

In the above, Vehicle.Master_ID is a FK to Master.ID

I've created a table variable like this:

declare @foo table (Master_Number varchar(20), First_Arrival datetime, Name varchar(20), Arrived datetime)

insert into
@foo
select
    M.Master_Number,
    M.First_Arrival,
    V.Name,
    V.Arrived
from
    Master as M
inner join
    Vehicle as V
on
    V.Master_ID = I.ID
order by M.Master_Number, V.Arrived

This gives me a nice list of all vehicles related to the same Master_Number, and the order they arrived in.

What I'm attempting to do is get something like this:

--------------------------------------------- Result --------------------------------------------------
Master_Number | First_Arrival | Vehicle_1_Name | Vehicle_1_Arrived | Vehicle_2_Name | Vehicle_2_Arrived

sorted where Vehicle_1_Arrived < Vehicle_2_Arrived, Vehicle_1_Name like '[ETL]%', and 'Vehicle_2_Name like '[TL]%'

The problem I'm running into is that there may be zero or more vehicles associated with a Master_ID, and I only want each row to be the first results that match this exactly, disregarding any other vehicles related to that Master_ID, and any null values.

I'm new to SQL, and can't wrap my head around this....any help would be greatly appreciated.

Upvotes: 0

Views: 119

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a very good candidate for apply:

select M.Master_Number, M.First_Arrival, v1.Name, v1.Arrived,
       v1.Name, v2.Arrived
from Master m cross apply
     (select top (1) v1.*
      from Vehicle v1
      where v.Master_ID = I.ID and v1.Name like '[ETL]%'
      order by v1.arrived
     ) v1 cross apply
     (select top (1) v2.*
      from Vehicle v2
      where v2.Master_ID = I.ID and v2.Name like '[TL]%' and
            v2.arrived > v1.arrived
      order by v2.arrived asc
     ) v2
order by M.Master_Number;

apply implements a lateral join, which is a lot like a correlated subquery, except it can return multiple columns and/or multiple rows.

Upvotes: 3

Related Questions