Reputation: 1343
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
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
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
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
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