CODEWITHSUNDEEP
sqldatabasepostgresql
Laurentius
Laurentius

Reputation: 45

compare two select in two tables

I need to return Cities with international flights departures

I know this is not correct, but this is what I have so far.

SELECT 
    flight.departurecity 
FROM 
    flight 
INNER JOIN 
    airport ON flight.departurecity = airport.city 
WHERE 
    (SELECT airport.nation WHERE flight.departurecity = airport.city) != 
        (SELECT airport.nation WHERE flight.arrivalcity = airport.city)

Airport table:

airport table

Flight table:

flight table

Upvotes: 2

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I would simply go for:

select a.*
from airport a
where exists (select 1
              from flight f join
                   airport a2
              where f2.arrivalcity = a2.city and
                    f2.departurecity = a.city and
                    a2.nation <> a.nation
             );

Note that this does not require select distinct, unless you really want a list of cities rather than just airports. So, it should be much faster than the double join version.

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23797

SELECT DISTINCT
    flight.departurecity 
FROM 
    flight 
INNER JOIN 
    airport a1 ON flight.departurecity = a1.city 
INNER JOIN 
    airport a2 ON flight.arrivalcity = a2.city 
WHERE 
    a1.nation != a2.nation;

Corrected and modified version what Gordon Linoff meant to suggest I guess:

select a.*
from airport a
where exists (select *
              from flight f join
                   airport a2 on f.arrivalCity = a2.city
              where f.departureCity = a.city
                    and a2.nation <> a.nation
             );

Upvotes: 1

Related Questions