How to JOIN two tables to find connecting flights?

Question

Table1 - Travelers

Columns - Name, NearestAriport, Destination

Table2 - Flights

Columns - Code, DepartureAirport, DepTime, ArrivalAirport, ArrTime

Problem - To find all possible ways to fly from NearestAiport to Destination.

I need to find all possible flights including DIRECT and CONNECTING flights for Travelers. I am able to find the Direct Flights but facing problem finding connecting flights. (Maximum 1 stop)

If some can help me write query for MS Access DB it would be really helpful.

Answer 1

You can try below:

Though the demo is on sqlserver but as it's standard sql I think it will work in MS Access too

DEMO

  select 
       X.name,
       x.nearestairport,
       case when X.destinationaiport=X.arrivaleairport then ''
       else X.arrivaleairport end as stop1,
      X.destinationaiport 
   from
    (
       select name, a.nearestairport, a.destinationaiport,b.arrivaleairport
       from table1 a left join table2 b
       on a.nearestairport=b.departureairport
    )X
    left join table2 c 
    on X.destinationaiport=c.arrivaleairport and X.nearestairport=c.departureairport

Output:

name    nearestairport  stop1   destinationaiport
Jason   JFK                     CLT
Jason   JFK              RDU    CLT
Robert  LAG                     RDU
Wel     CAK              JFK    CLT
Adam    RDU              CLT    JFK
Adam    RDU                     JFK