CODEWITHSUNDEEP
mysqlsql-serverouter-apply
suraj
suraj

Reputation: 11

Alternate way for outer apply in SQL Server

I have a query in SQL server in which I have used outer apply. Now I want to convert it so that query can run on SQL server and MySQL also.

select top 5 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

I have to covert this query so I can run on both databases.

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
MH14 aa 1111 aaa
MH12 bb 2222 ccc
MH13 cc 3333 eee
MH42 dd 4444 ggg

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

Upvotes: 0

Views: 1849

Answers (2)

bmsqldev
bmsqldev

Reputation: 2735

If you want first record from location history table for each vehicle which is present in vehicles table, then you can use cross join.

see below e.g

create table #location (vehicle_id int, vehicle_name varchar(50))

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



insert into #location 
values
(1, 'car'), 
(2,'bus'),
(3,'auto'), 
(4,'jeep')

insert into #lochistory
values
 ( 1, 'india', getdate()),
 ( 1, 'usa'  , getdate()),
 ( 2, 'india', getdate())



select *from #location l
cross join
(
 select top 1 * from #lochistory 
)b

the output will be as below.

vehicle_id  vehicle_name    vehicle_id  location    date
1           car             1           india       2016-04-13 05:21:57.650
2           bus             1           india       2016-04-13 05:21:57.650
3           auto            1           india       2016-04-13 05:21:57.650
4           jeep            1           india       2016-04-13 05:21:57.650

Upvotes: 0

irakliG.
irakliG.

Reputation: 176

In this case you can use LEFT JOIN instead of OUTER APPLY. like that:

select top 5 v.sVehicleName as VehicleNo, ll.Location 
from vehicles v
left join
     (
     select vehicle_id, min(Location) as Location
     from location_history 
     group by vehicle_id
     ) ll 
on ll.vehicle_id = v.vehicle_id

Upvotes: 1

Related Questions