Find records in the main table that match records in another table in SQL Server
I have a SQL Server database with three tables: Rides , Fragments, and Legs .
Each Send has a one-to-large relationship with Fragments and Slices has a one-to-large relationship with legs . >.
The trips represent the complete journey, the slice represents only the outbound or return portions of the trip, and the legs represent all stops in the outbound or return slices.
I want to find all trips with matching legs.
Look at the tables:
Journey
tripId saleTotal queryDate
1 $200 6/10/2015
2 $198 6/11/2015
Slices
sliceId connections duration tripIdFK
1 1 50 1
2 1 45 1
3 0 60 2
4 1 85 2
Legs
legId carrier flightNumber departureAirport departureDate ArrivalAirport ArrivalDate sliceIDFK
1 AA 1 JFK 7/1/2015 LON 7/2/2015 1
2 AA 2 LON 7/2/2015 FRA 7/2/2015 1
3 AA 11 FRA 7/10/2015 LON 7/10/2015 2
4 AA 12 LON 7/10/2015 JFK 7/10/2015 2
5 UA 5 EWR 8/1/2015 LAX 8/1/2015 3
6 UA 6 LAX 8/5/2015 ORD 8/5/2015 4
7 UA 7 ORD 8/5/2015 EWR 8/5/2015 4
How can I find all trips where all operator and flight numbers are the same, for example in legId 1-4, by searching for departureAirport / arrivalAirport (JFK / FRA)?
In other words, legId 1-4 is one detail unit for Trip 1, and legId 5-7 is another detail block for Trip 2. I need to find which other trips match exactly legId 1-4 details (except PC and FK), etc. Any help would be greatly appreciated!
source to share
Oh my brain hurts ...
Replace all question marks (3 of them) with the trip ID of the trip where you want to check similar trips.
select distinct s.tripIDFK as tripId
from Legs l
left join Slices s on l.sliceIDFK = s.sliceId
where s.tripIDFK != ?
and not exists (
select carrier, flightNumber, departureAirport, departureDate
from Legs l2
left join Slices s2 on l2.sliceIDFK = s2.sliceId
where s2.tripIDFK = s.tripIDFK
except
select carrier, flightNumber, departureAirport, departureDate
from Legs l2
left join Slices s2 on l2.sliceIDFK = s2.sliceId
where s2.tripIDFK = ?
)
and not exists (
select carrier, flightNumber, departureAirport, departureDate
from Legs l2
left join Slices s2 on l2.sliceIDFK = s2.sliceId
where s2.tripIDFK = ?
except
select carrier, flightNumber, departureAirport, departureDate
from Legs l2
left join Slices s2 on l2.sliceIDFK = s2.sliceId
where s2.tripIDFK = s.tripIDFK
)
order by s.tripIDFK
A soft request is an offer and not exists
. They get leg data from one trip and efficiently subtract leg data from another trip using a suggestion except
. If you have nothing left, then the second trip data contains all the first trip data. You must run the statement twice and not exists
(with inverse operands) to ensure that the two trip datasets are indeed identical, and that they are not just a subset of the other.
It doesn't scale in any way for a lot of rows.
source to share
Hope it helps.
Just pass the base TripId (in @BaseTripID) that you want to compare other records with. I am assuming that you are only concerned with the carrier, FlightNumber, departureAirport, ArrivalAirport to match any other trip exactly, regardless of date fields.
create table Trips(tripId int,saleTotal int,queryDate date)
create table Slices(sliceId int ,connections int,duration int ,tripIdFK int)
create table Legs(legId int, carrier char(2), flightNumber int, departureAirport char(3), departureDate date, ArrivalAirport char(3), ArrivalDate date, sliceIDFK int)
insert into Trips values(1,200,'6/10/2015'),(2,198,'6/11/2015'),(3,300,'6/15/2015'),(4,200,'6/21/2015')
insert into Slices values(1,1,50,1),(2,1,45,1),(3,0,60,2),(4,1,85,2),(5,1,50,3),(6,1,45,3),(7,1,45,4),(8,1,45,4)
insert into Legs values(1,'AA',1,'JFK','7/1/2015','LON','7/2/2015',1) ,
(2,'AA',2,'LON','7/2/2015','FRA','7/2/2015',1),
(3,'AA',11,'FRA','7/10/2015','LON','7/10/2015',2),
(4,'AA',12,'LON','7/10/2015','JFK','7/10/2015',2),
(5,'UA',5,'EWR','8/1/2015','LAX','8/1/2015',3),
(6,'UA',6,'LAX','8/5/2015','ORD','8/5/2015',4),
(7,'UA',7,'ORD','8/5/2015','EWR','8/5/2015',4),
(8,'AA',1,'JFK','7/11/2015','LON','7/12/2015',5),
(9,'AA',2,'LON','7/12/2015','FRA','7/12/2015',5),
(10,'AA',11,'FRA','7/20/2015','LON','7/20/2015',6),
(11,'AA',12,'LON','7/20/2015','JFK','7/20/2015',6),
(12,'AA',1,'JFK','7/1/2015','LON','7/2/2015',7) ,
(13,'AA',2,'LON','7/2/2015','FRA','7/2/2015',7),
(14,'AA',11,'FRA','7/10/2015','BEL','7/10/2015',8),
(15,'AA',12,'BEL','7/10/2015','JFK','7/10/2015',8)
--select * from Trips
--select * from Slices
--select * from Legs
-------------------------------------------------------------------
Declare @BaseTripID int = 1, @Legs int ,@MatchingTripID int
declare @BaseTrip table(carrier char(2), flightNumber int, departureAirport char(3), ArrivalAirport char(3),row_no int)
declare @MatchingTrip table(carrier char(2), flightNumber int, departureAirport char(3), ArrivalAirport char(3),row_no int,legid int,tripid int)
insert into @BaseTrip
select carrier, flightNumber, departureAirport, ArrivalAirport,ROW_NUMBER() over(order by l.legId)
from Legs l join slices s on s.sliceId = l.sliceIDFK
where s.tripIdFK = @BaseTripID
select @Legs=count(*) from @BaseTrip
Insert into @MatchingTrip
select carrier, flightNumber, departureAirport, ArrivalAirport,ROW_NUMBER() over(partition by s.tripIdFK order by l.legId) as row_no,l.legId,s.tripIdFK
from Legs l join slices s on s.sliceId = l.sliceIDFK
and s.tripIdFK in
(select s.tripIdFK
from Legs l join slices s on s.sliceId = l.sliceIDFK
and s.tripIdFK <> @BaseTripID
Group by s.tripIdFK having count(l.legId)=@Legs)
select @MatchingTripID = m.tripid
from @MatchingTrip m join @BaseTrip b
on m.carrier = b.carrier
and m.flightNumber = b.flightNumber
and m.departureAirport = b.departureAirport
and m.ArrivalAirport = b.ArrivalAirport
and m.row_no = b.row_no
GROUP BY m.tripid HAVING COUNT(*) = @Legs
select s.tripIdFK as matchingTripID,l.legid,l.carrier,l.flightNumber,l.departureAirport,l.ArrivalAirport
from Legs l
join Slices s on s.sliceId = l.sliceIDFK
where s.tripIdFK = @MatchingTripID
---------------------
drop table Trips
drop table Slices
drop table Legs
using a foot counter is key. This way, we eliminate any matches other than completely identical legs (trip 4 with two matching legs). So now we only get Trip 3 as matching entries.
Please note that we also exclude rides that have extra legs other than the respective ones. Hope this is what you expect, a couple of perfectly identical rides.
source to share
Another approach is to define / store a footKey with each chunk so that you can find the matching chunks as you travel.
For each chunk, your legs should be associated with each foot carrier and flight number that you can use using a "XML path" like this
something like:
select
distinct
stuff((
select ',' + l.carrier + ':' + l.flightNumber
from legs l
where l.carrier = carrier and l.flightnumber = flightnumber
order by l.carrier, l.flightnumber
for xml path('')
),1,1,'') as legsList
from legs
group by carrier, flightnumber
source to share