CODEWITHSUNDEEP
mysqlsqlsql-serverleft-joinouter-apply
Swapnil
Swapnil

Reputation: 9

alternative for outer apply

This is my tables

create table #vehicles (vehicle_id int, sVehicleName varchar(50))

create table #location_history ( vehicle_id int, location varchar(50), date datetime)

insert into #vehicles values
    (1, 'MH 14 aa 1111'),
    (2,'MH 12 bb 2222'),
    (3,'MH 13 cc 3333'),
    (4,'MH 42 dd 4444')

insert into #location_history values
    ( 1, 'aaa', getdate()),
    ( 1, 'bbb' , getdate()),
    ( 2, 'ccc', getdate()),
    ( 2, 'ddd', getdate()),
    (3, 'eee', getdate()),
    ( 3, 'fff', getdate()),
    ( 4, 'ggg', getdate()),
    ( 4 ,'hhh', getdate())

This is query which I execute in SQL server.

select v.sVehicleName as VehicleNo, ll.Location
from #vehicles v outer APPLY
     (select top 1 Location from #location_history where vehicle_id = v.vehicle_id
     ) ll

This is output in SQL server.

  VehicleNO|Location
MH14aa1111 |  aaa
MH12bb2222 | ccc
MH13cc3333 | eee
MH42dd4444  |ggg

I want to execute this in MySQL. and I want same output mentioned above.

Upvotes: 0

Views: 3504

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

First, the SQL Server query doesn't actually make sense, because you are using top without an order by.

Presumably, you intend something like this:

select v.sVehicleName as VehicleNo, ll.Location
from #vehicles v outer APPLY
     (select top 1 Location
      from #location_history
      where vehicle_id = v.vehicle_id
      order by ??  -- something to indicate ordering
     ) ll;

You need a method to get the latest record for each vehicle. Under normal circumstances, I think date would contain this information -- however, this is not true in your sample data.

Assuming that date really does contain unique values, then you can do:

select v.sVehicleName as VehicleNo, ll.Location
from vehicles v join
     location_history lh
     using (vehicle_id)
where lh.date = (select max(lh2.date)
                 from location_history lh2
                 where lh2.vehicle_id = lh.vehicle_id
                );

Otherwise, you can do what you want using a correlated subquery. However, this will return an arbitrary matching value on the most recent date:

select v.sVehicleName as VehicleNo,
       (select ll.Location
        from location_history lh2
        where lh2.vehicle_id = lh.vehicle_id
        order by date desc
        limit 1
       ) as location
from vehicles v ;

Upvotes: 2

Related Questions