SQL join 2 times same table

I have a flight table like

CREATE TABLE flight (id int, name varchar(25), origin int, destination int);
INSERT INTO flight (id, name, origin, destination) VALUES (1, 'Alpha', 1,2);
INSERT INTO flight (id, name, origin, destination) VALUES (2, 'Beta', 3,4);
...

And airport table like

CREATE TABLE airport (id int, name varchar(25));
INSERT INTO airport (id, name) VALUES (1, 'Amsterdam');
INSERT INTO airport (id, name) VALUES (2, 'London');
INSERT INTO airport (id, name) VALUES (3, 'Paris');
INSERT INTO airport (id, name) VALUES (4, 'Dubai');
...

How can I get all airports that made a flight to or brought in a flight from Amsterdam?

If I have a query with the where clause WHERE flight.origin = 1 OR flight.destination = 1 I also get Amsterdam in my results, while there is no flight from Amsterdam to Amsterdam.

Answer

Use an IN clause:

select *
from airport
where id in (select origin from flight where destination = 1)
   or id in (select destination from flight where origin = 1);

select *
from airport
where id in 
(
  select origin from flight where destination = 1
  union all
  select destination from flight where origin = 1
);

(You can of course replace = 1 with = (select id from airport where name = 'Amsterdam').)

Advertisement

Answer