SQL query question, how to make a query that outputs all entries of two tables

Question

I have two tables, CITIES and FLIGHTS:

CITIES

id	name
1	New York
2	Paris
3	Tokyo
4	Amsterdam

FLIGHTS

id	departure_id	arrival_id
1	1	2
2	1	3
3	2	3
4	2	4

I need to write a query that finds all the flight connections.

The desired output would be:

departure_city	arrival_city
New York	Paris
New York	Tokyo
Paris	Tokyo
Paris	Amsterdam

How to write this query?

Answer 1

You can use join for that. Both inner join and left join will serve your purpose:

Query with left join:

Select c.name as departure_city, ct.name as arrival_city from FLIGHTS f 
left join CITIES c on f.departure_id=c.id
left join CITIES ct on f.arrival_id = ct.id

Outupt:

departure_city	arrival_city
New York	Paris
New York	Tokyo
Paris	Tokyo
Paris	Amsterdam

Query with inner join:

Select c.name as departure_city, ct.name as arrival_city from FLIGHTS f 
inner join CITIES c on f.departure_id=c.id
inner join CITIES ct on f.arrival_id = ct.id

Output:

departure_city	arrival_city
New York	Paris
New York	Tokyo
Paris	Tokyo
Paris	Amsterdam

db<>fiddle here

Answer 2

You can do two joins:

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;

Without further information, it's unclear if you want to do a left or inner join, if you need a where clause, if you need an order by etc. Maybe it would be better to learn some SQL basics and then ask a more precise question if necessary. Try out if you want: db<>fiddle

Answer 3

Also you can use below SQL:

SELECT c1.NAME, c2.NAME FROM CITIES c1, CITIES c2, flights f WHERE c1.id = f.departure_id AND c2.id = f.arrival_id;

The prepared statement is as below:

CREATE TABLE CITIES(id int, name varchar(32));

INSERT INTO CITIES values(1, 'New York'), (2, 'Paris'), (3, 'Tokyo'), (4, 'Amsterdam');

CREATE TABLE FLIGHTS(id int, departure_id int, arrival_id int);

INSERT INTO FLIGHTS VALUES(1,1,2), (2,1,3), (3,2,3), (4,2,4);