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?
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
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
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);
