mysql - SQL Join多个外来ID

Question

我有两张桌子。

tbl_城市：

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

产生如下输出的 SQL 是什么：

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

标签： mysqlsql

Answer 1

加入两次 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