For experimentation I'm building a script which is designed to find flight connections using PHP & mysql.
I have a database called "schedules" similar to the following:
dep_airport, arr_airport, flt_num
LHR JFK 100,
LHR FRA 200,
JFK MIA 300
Lets assume someone wants to fly from LHR to MIA. They would need to connect in JFK, so take flights 100 LHR - JFK, and then change to flight 300 JFK - MIA.
If I run a query:
SELECT * FROM schedules WHERE dep_airport = 'LHR' AND arr_airport = 'MIA'
The query will return null, as there is no direct connection. How can I query the database for it to find the relationship that LHR - MIA does exist but via JFK?
To really make it work, you'll need more info - arrival and departure times for instance.
If you limit your choices to one-stops (max two flights), then a simple SQL join will find your flights.
SELECT *
FROM `schedules` a
JOIN `schedules` b
ON a.`arr_airport` = b.`dep_airport`
WHERE a.`dep_airport` = 'LHR' AND b.`arr_airport` = 'MIA'
Of course, you would check for direct flights before running this query. Also, you would need to put constraints on the arrival and departure times. Real scheduling software even takes the time to get from one gate to another into consideration, so it isn't just as simple as arrival time < departure time.
If you also need to consider more stops, you would add more joins for queries that would be run after this one.
It is also possible to write a query that would look for non-stops and one stops all at once, but you might want to avoid that for performance reasons.
Given your current schema and the way that you clarified your question in comments you could get the information by using a LEFT JOIN on the table in question:
SELECT s1.*
FROM schedules s1
LEFT OUTER JOIN schedules s2
ON s1.arr = s2.dep
WHERE s1.dep = 'LHR'
AND s2.arr = 'MIA'
As shown in this example it returns:
dep arr flight
LHR JFK 100
Added another example with an additional flight to show how this would work at most basic level. Again, there are many unaccounted for variables that were asked about in comments but if all you're after is what was asked with the three original columns then this could be the way you would want to go.