Trying to find all cities from which there is no direct flight from the city (PostgreSQL)

Question

I am trying to write a query that determines which cities I cannot fly directly from the city, say, to London. Given the scheme:

cities:

| c_id | city_name | 

flights:

 | f_id | departure_city_id | destination_city_id | 

currently my query returns the opposite, i.e. returns cities for which there is a direct flight from London

 SELECT c2.city_name as "City" FROM flights AS f JOIN cities AS c2 ON f.destination_city_id != c2.c_id JOIN cities AS c ON c.c_id = c.c_id WHERE c.city_name = 'London' AND c.c_id != c2.c_id AND f.departure_city_id = c.c_id; 

I would have thought it would be easy to change it to get what I want. I thought to change the third line to

 JOIN cities AS c2 ON f.destination_city_id = c2.c_id 

Would do the trick, but it’s not. Any help?

Answer 1

Cities I cannot fly directly from a city, say, to London.

So you can fly there, and not directly from London. So JOIN (not LEFT JOIN ) city to flight via destination_city_id :

 SELECT DISTINCT c.city_name FROM cities c JOIN flights f ON f.destination_city_id = c.c_id JOIN cities c2 ON c2.c_id = f.departure_city_id WHERE c2.city_name <> 'London'; 

Then I just need to exclude flights from London, apply DISTINCT to get unique city names, and we are done.

A more complicated interpretation of this question would be:
"Cities from which you can fly from London are simply not direct."
But since this looks like basic homework, I do not assume that they expect a recursive request from you.

Answer 2

Try something like:

 SELECT * FROM cities c WHERE c.c_id NOT IN (SELECT f.destination_city_id FROM flights f JOIN cities c2 ON f.departure_city_id = c.c_id WHERE c2.city_name = 'London')