Stuck on beginner SQL practice. Multiple table where columns use same id

Question

I'm very sorry to bother with minor problem, but I tried to search old answers for this one and since my skills in SQL are complete 0, I didn't even understand the answers :/! Neither is my English terminology great enough for properly searching.

I have these 2 tables: Cities and Flights.

Cities
+----+-------------+
|id  |    name     |
+----+-------------+
|1   |    Oslo     |
|2   |   New York  |
|3   |   Hong Kong | 
+----+-------------+
Flights
+----+--------------------+-------------------+
|id  |    wherefrom_id    |   whereto_id      |
+----+--------------------+-------------------+
|1   |  3                 |  2                |
|2   |  3                 |  1                |
|3   |  1                 |  3                |
+----+--------------------+-------------------+

Now I have to write code where I need to make city ID's merge to wherefrom_id and whereto_id, in that manner that the answer shows table where you can see list of Flights (FROM/TO). Example:

ANSWER:
+-----------+----------------+
|HONG KONG  |    NEW YORK    |
+-----------+----------------+
|HONG KONG  |    OSLO        |
+-----------+----------------+
|OSLO       |   HONG KONG    |
+-----------+----------------+

This is what I wrote:

SELECT C.name, C.name 
FROM Cities C, Flights F 
WHERE C.id = F.wherefrom_id AND C.id = F.whereto_id;

For some reason this doesnt seem to work and I get nothing showing on my practice program. There is no error or anything it just doesnt show anything on the test answer. I really hope you get what I mean, English is not my first language and I truly tried my best to make it clear as possible :S

Answer 1

First things first - it's a lot easier to code in standard SQL join syntax. Converting your above to that is

SELECT C.name, C.name 
FROM   Cities C
       INNER JOIN Flights F ON C.id = F.wherefrom_id AND C.id = F.whereto_id;

The question you've been asked requires logic people don't usually use at first so it can be confusing the first time you encounter it.

I will run through the logic jump in a moment.

Imagine your Flights table has the City names in it (not IDs).

• It would have columns, say, FlightID, From_City_Name, To_City_Name.
• An example row would be 1, 'Oslo', 'Prague'.

Getting the data for this would be easy e.g., SELECT Flight_ID, From_City_Name, To_City_name FROM Flights.

However, this has many problems. As your question has done, you decide to pull out the cities into their own reference tables.

For this first example, however, you decided to have two extra tables as reference tables: From_City and To_City. These would both have an ID and city name. You then change your Flights to refer to these.

Your code would look like

SELECT F.ID, FC.Name AS From_City, TC.Name AS To_City
FROM   Flights
       INNER JOIN From_City AS FC ON Flights.From_City_ID = FC.ID
       INNER JOIN To_City AS TC ON Flights.To_City_ID = TC.ID

Notice how there are two joins there - one to From_City and one to To_City? That is because the From and To cities are referring to different things in the data.

So, then the final part of the issue: why have two city tables (from and to). Why not have one? Well, you can. If you create just one table, and modify the above, you get something like this:

SELECT F.ID, FC.Name AS From_City, TC.Name AS To_City
FROM   Flights
       INNER JOIN City AS FC ON Flights.From_City_ID = FC.ID
       INNER JOIN City AS TC ON Flights.To_City_ID = TC.ID

Note that all that has changed is that the From_City and To_City references have been pointed to a different table City. However, the rest is the same.

And that, actually, would be your answer. The complex part that most people don't get to straight away, is having two joins to the same table.

---

As an aside, your original code is technically valid.

SELECT C.name, C.name 
FROM   Cities C
       INNER JOIN Flights F ON C.id = F.wherefrom_id AND C.id = F.whereto_id;

However, what it's effectively saying is to get the city names where the From_City is the same as the To_City - which is obviously not what you want (unless you're looking for turnbacks).

Answer 2

What you're doing is an old SQL way of expressing joins. The standard now has better ways to declare the relationships within the from clause and I take it that your material has postponed that slightly:

There are people who will yell at you for using this ancient syntax but the answer is easy enough:

SELECT C1.name, C2.name 
FROM Cities C1, Cities C2, Flights F 
WHERE C1.id = F.wherefrom_id AND C2.id = F.whereto_id

You can think of this as creating a "cross product" of all city-pair combinations and matching up the ones that match actual flights. The key is to references Cities twice by using different aliases (or correlation names.)

Answer 3

I think this is what you are looking for..

SELECT wf.name "wherefrom", wt.name "whereto" 
FROM Flights f 
JOIN Cities wf 
ON f.wherefrom_id = wf.id 
JOIN Cities wt 
ON f.whereto_id = wt.id
order by f.id