CODEWITHSUNDEEP
mysql
Sora
Sora

Reputation: 95

JOIN one tables where two columns are id in other table

First of all, any suggestion to improve my question title is more than welcome. Here is my explanation.

I have two tables. One is flights and the other is airports.

flights
-------------------------------------------------------------------
id | departure_id | destination_id | departure_date | arrival_date
-------------------------------------------------------------------
1  |1             | 5              | 2014-05-01     | 2014-05-01
-------------------------------------------------------------------

airports
-----------------------------------
id | country_id | name | timezone |
-----------------------------------
1  | 1          | TPE  | +08:00   |
-----------------------------------
5  | 2          | HND  | +09:00   |
-----------------------------------

and I need this result:

-----------------------------------------------------------
departure_date | departure_tz | arrival_date | arrival_tz |
-----------------------------------------------------------
2014-05-01     | +08:00       | 2014-05-01   | +09:00   |
-----------------------------------------------------------

I have this query, but is not giving me the result I want because I do not how to make the relation between destination_id, departure_id with the table aiports, both of them are id in the airports table, any suggestion please. This is the query I have but with no distinction between timezone that is the info I want to show the most.

SELECT flights.flight_number,
  flights.departure_date,
  airports.timezone as departure_tz,
  flights.arrival_date,
  airports.timezone as arrival_tz
FROM flights
JOIN airports

Upvotes: 0

Views: 71

Answers (3)

Alex
Alex

Reputation: 17289

SELECT flights.flight_number,
  flights.departure_date,
  dep.timezone as departure_tz,
  flights.arrival_date,
  arvl.timezone as arrival_tz
FROM flights
LEFT JOIN airports dep
ON flights.departure_id = dep.id
LEFT JOIN airports arvl
ON flights.destination_id = arvl.id

Upvotes: 1

Máté Juhász
Máté Juhász

Reputation: 2327

SELECT flights.flight_number,
flights.departure_date,
A1.timezone as departure_tz,
flights.arrival_date,
A2.timezone as arrival_tz
FROM (flights JOIN airports as A1 ON flights.departure_id=A1.id)
JOIN airports as A2 on flights.departure_id=A2.id

ON where missing from your query and also as you want to join to two separate rows from airports you need to use two instances of that table.

Upvotes: 1

Kristian Lilov
Kristian Lilov

Reputation: 610

Maybe 2 joins?

SELECT 
t1.id AS flight_number 
t1.departure_date,
t2.timezone AS departure_tz 
t1.arrival_date
t3.timezone AS arrival_tz
FROM flights t1
LEFT JOIN airports t2
ON t1.departure_id = t2.id
LEFT JOIN airports t3
ON t1.arrival_id = t3.id

Upvotes: 0

Related Questions