MySQL Using AND Statement

MySQLrelational-division

I am having issues with this question:

Find the cities that have direct (non-stop) flights to both Honolulu and Newark.

enter image description here

The answer should be Dallas, since its the only location that has destinations to both Honolulu and Newark. I'm trying to use the AND statement but it wont work. 

SELECT FLIGHTS.source_city
FROM FLIGHTS
WHERE (FLIGHTS.destination_city = 'Honolulu') and (FLIGHTS.destination_city = 'Newark')

I am using MySQL.

Best Answer

You cannot use AND. The construction WHERE (FLIGHTS.destination_city = 'Honolulu') and (FLIGHTS.destination_city = 'Newark') which you tries to use means that you want to find a record in which destination is both Honolulu and Newark IN THE SAME RECORD. Of course, it is impossible.

If the destinations count is strongly 2, the danblack's solution seems to be optimal.

If the destinations count vary, you must use some another method.

For MySQL it is: select records, where destination is Honolulu OR Newark OR another city which you need, then count the number of unique destination cities in the filtered records for each separate source city, and select only records where this count is equal to the number of destination cities:

SELECT flights.source_city
FROM flights
WHERE flights.destination_city IN ('Honolulu', 'Newark', 'Midland')
GROUP BY flights.source_city
HAVING COUNT(DISTINCT flights.destination_city) = 3;

fiddle

Related Question