SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table cities (id int, name varchar(100));
create table flights (id int, departure_id int, arrival_id int);
insert into cities values (1,'New York');
insert into cities values (2,'Paris');
insert into cities values (3,'Tokyo');
insert into cities values (4,'Amsterdam');
4 rows affected

insert into flights values (1,1,2);
insert into flights values (2,1,3);
insert into flights values (3,2,3);
insert into flights values (4,2,4);
4 rows affected

SELECT departure.name AS departure, 
arrival.name AS arrival
FROM cities AS departure 
JOIN flights f ON departure.id = f.departure_id
JOIN cities arrival ON arrival.id = f.arrival_id;


departure
arrival


New York
Paris

New York
Tokyo

Paris
Tokyo

Paris
Amsterdam