Alternate way for outer apply in SQL Server

Question

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.

in vehicle table there are two columns vehicle_id , vehicle_name in location_history there are two columns vehicle_id , location , date in location history table there can be multiple entries for each vehicle — suraj, Apr 13 '16 at 09:35
I want first record from location history table for each vehicle which is present in vehicles table. — suraj, Apr 13 '16 at 09:38

irakliG. · Answer 1 · 2016-04-13T07:20:25.410

1

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

edited Apr 13 '16 at 07:20

answered Apr 13 '16 at 06:49

irakliG.

176
10

location is varchar . how can we apply max on location. I have checked your query but it is giving wrong output. – suraj Apr 13 '16 at 07:01
max(x) finds last value in alphabetical ordered set of strings and min(x) finds first value. If column [LOCATION] in [location_history] table is ascending ordered you can use min(Location) instead of "top 1 Location". – irakliG. Apr 13 '16 at 07:20
it seems in [location_history] each [vehicle_id] has many unordered [Location]. in this case my solution is wrong. This is only for ordered column. I don't know if there is solution to convert your query in mysql without changing meaning. – irakliG. Apr 13 '16 at 07:54

score 0 · Answer 2 · answered Apr 13 '16 at 12:11

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

Alternate way for outer apply in SQL Server

2 Answers2