CODEWITHSUNDEEP
mysqlsql
James
James

Reputation: 1343

SQL Join multiple foreign IDs

I have two tables.

tbl_cities:

Id, Name
---------
1  | Paris
2  | London
3  | Amsterdam
4  | Mumbai

tbl_journeys:

from_id, to_id, Journey_date
---------------------------------
1     |    2   |  1 Mar 2019
2     |    1   |  1 Mar 2019
3     |    1   |  3 Mar 2019

What is the SQL to produce output like this:

 City_From_Name, City_To,Name, Journey_Date
------------------------------------------------
Paris          | London       | 1 Mar 2019
London         | Paris        | 1 Mar 2019
Amsterdam      | London       | 3 Mar 2019

Upvotes: 0

Views: 50

Answers (4)

hamdan
hamdan

Reputation: 323

Try this

Select tbl_cities.Name as "City_from_Name" , tbl_journeys.to_Id as "City_to_Name" , tbl_journeys.Journey_date from tbl_cities , tbl_journeys where tbl_cities.Id = tbl_journeys.from_id;

Upvotes: 0

Hemang A
Hemang A

Reputation: 1012

Please try this.

Select B.Name As City_From_Name, C.Name As City_To_Name, A.Journey_Date from tbl_journeys A
LEFT JOIN  tbl_cities B
ON B.id = A.From_id
LEFT JOIN  tbl_cities  C
ON C.id = A.To_id

Upvotes: 1

godot
godot

Reputation: 3545

SELECT c1.Name City_From_Name , c2.Name City_To,Name, j.Journey_date Journey_Date
FROM tbl_journeys j
       INNER JOIN tbl_cities c1 ON c1.Id = j.from_id
       INNER JOIN tbl_cities c2 ON c2.Id = j.to_id

Upvotes: 1

Fahmi
Fahmi

Reputation: 37493

join twice with tbl_cities

select b.name as City_From_Name,b1.name as City_To,Name,journey_date
from 
tbl_journeys a inner join tbl_cities b on a.from_id=b.id
inner join tbl_cities b1 on a.to_id=b1.id

Upvotes: 1

Related Questions