-2

I'm learning MySQL for the first time, and I've been able to figure things out so far, but I've ran into a practice assignment that I just can't seem to wrap my head around.

I've viewed multiple similar issues here, but I've had trouble adapting them to my own situation.

I have two tables:

Cities

+----+-----------+
| id | name      |
+----+-----------+
| 1  | Helsinki  |
+----+-----------+
| 2  | Stockholm |
+----+-----------+
| 3  | Oslo      |
+----+-----------+
| 4  | London    |
+----+-----------+

Flights

+----+---------+----------+
| id | from_id | to_id    |
+----+---------+----------+
| 1  | 1       | 2        |
+----+---------+----------+
| 2  | 1       | 3        |
+----+---------+----------+
| 3  | 2       | 3        |
+----+---------+----------+
| 4  | 2       | 4        |
+----+---------+----------+
| 5  | 1       | 2        |
+----+---------+----------+
| 6  | 2       | 1        |
+----+---------+----------+
| 7  | 1       | 3        |
+----+---------+----------+
| 8  | 3       | 1        |
+----+---------+----------+

I want to create a query which has an end result like this:

+-----------+-----------+
| FROM      | TO        |
+-----------+-----------+
| Helsinki  | Stockholm |
+-----------+-----------+
| Helsinki  | Oslo      |
+-----------+-----------+
| Stockholm | Oslo      |
+-----------+-----------+
| Stockholm | London    |
+-----------+-----------+
| Helsinki  | Stockholm |
+-----------+-----------+
| Stockholm | Helsinki  |
+-----------+-----------+
| Helsinki  | Oslo      |
+-----------+-----------+
| Oslo      | Helsinki  |
+-----------+-----------+

Basically I just want to replace the id numbers with city names, but I want to do this only in the query, don't want to replace any values in the data itself. How do I achieve this?

Here you can find a test case for my situation: http://sqlfiddle.com/#!9/7475a2/5/0

It should come with everything necessary for creating and populating the tables and also one of the many queries I've tried, but which I had no success with.

Links to some similar topics I've looked at:

Even after trying to adapt solutions from these topics, and reading up on "SELECT" and "JOIN", I can't get it right.

Zoe
  • 27,060
  • 21
  • 118
  • 148
SleepyZ
  • 1
  • A [mre] belongs in a question, not just at a link. PS Just listing linksis not showing research effort, summarize & explain how they didn't help. Again, don't have relevant content not in your post, make your post self-contained. – philipxy Jun 18 '22 at 18:16
  • Re (re)search success: Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Jun 18 '22 at 18:16

2 Answers2

0

Join the Flights table to the Cities table, twice:

SELECT c1.name, c2.name
FROM Flights f
INNER JOIN Cities c1 ON c1.id = f.from_id
INNER JOIN Cities c2 ON c2.id = f.to_id
ORDER BY f.id;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0
select fromCity.name , toCity.name  
from Flights
inner join Cities as fromCity on Flights.from_id = fromCity.id 
inner join Cities as toCity on Flights.to_id = toCity.id
cnt
  • 11
  • 1
  • This is clear to understand and works exactly as I needed it to, thank you! I did a slight modification to the first line to get the titles named as well: `SELECT fromCity.name AS 'FROM' , toCity.name AS 'TO'` – SleepyZ Jun 18 '22 at 16:09