SQLize Online / PHPize Online  /  SQLtest Online

WITH all_airports AS ( SELECT a1.airport_code AS departure_airport, a2.airport_code AS arrival_airport FROM airports_data a1 CROSS JOIN airports_data a2 WHERE a1.airport_code != a2.airport_code ), direct_flights AS ( SELECT DISTINCT departure_airport, arrival_airport FROM flights ), no_direct_flights AS ( SELECT aa.departure_airport, aa.arrival_airport FROM all_airports aa LEFT JOIN direct_flights df ON aa.departure_airport = df.departure_airport AND aa.arrival_airport = df.arrival_airport WHERE df.departure_airport IS NULL ) SELECT dep.airport_name->>'en' AS departure_airport, arr.airport_name->>'en' AS arrival_airport FROM no_direct_flights ndf JOIN airports_data dep ON ndf.departure_airport = dep.airport_code JOIN airports_data arr ON ndf.arrival_airport = arr.airport_code GROUP BY (dep.airport_name, arr.airport_name) ORDER BY departure_airport, arrival_airport LIMIT 5047;