[SOLVED] selecting transfer flights

[RichardRotterdam]

Allright usually i dont have much trouble with sql but this one is bugging me.

 

I have a table with flight data which has the follwing fields(filtered out what's not needed)

 

flight_id

departure_airport_id

destination_airport_id

departure_country_id

destination_country_id

departure_timestamp

arrival_timestamp

 

selecting a flight with a selected departure and destination airports on a date isnt difficult.

the trouble starts when i want a selection of flights where you have to transfer to another plane.

does anyone have any ideas?

 

thanks in advance

 

[DSGameMaker]

Got any code for us to work with?

[gethinw]

Depends whether you know the route in advance or not, or if you're saying "i want to go from a to b, via any of c, d, e, f,...".

 

If you know, you could probably do it with a self join, something along the lines of:

 

SELECT * FROM table t1 INNER JOIN table t2 ON t1.destination_airport=t2.departure_airport AND t1.departure_timestamp<t2.departure_timestamp WHERE t1.destination_airport=required_destination AND t2.destination_airport=required_destination AND t1.destination_timestamp > required_destination

 

That's just suggestion off the top of my head, so not sure if it would work, and depending on the size of your database it might be simpler and faster to run two (or more, depending on the number of legs) separate queries, as the JOIN could potentially create a fairly large dataset.

 

Actually, now I think about it, that should work even if you don't know the route, although only if there's just one stopover. Any more and it starts to get a bit nasty...

[RichardRotterdam]

ty    inner join was what i was looking for.

Here is the query in case someone has a similar problem in the future

 

select
    *
    from
      flights_full_data as first_flights
    inner join
      flights_full_data as second_flights
      on first_flights.destination_airport_id=second_flights.departure_airport_id
    where first_flights.departure_airport_id=DEPARTURE_AIRPORT_VAR
      and second_flights.destination_airport_id=DESTINATION_AIRPORT_VAR
      and first_flights.flight_arrival_timestamp <= second_flights.flight_arrival_timestamp + interval 2 hour
      and first_flights.flight_departure_date=DEPARTURE_DATE_VAR