MySql连接具有多列的两个表

Question

我有两个表

问题：

id |   name        
----------------
1 | Bahawalpur   
2 | Multan
3 | Karachi
4 | Lahore

tbl灯光：

id | from_city_id | to_city_id
-------------------------------
1 |      1       |     2 
2 |      3       |     4
3 |      2       |     1

我想连接表，以便它显示两列的城市名称，即：from_city_id&to_city_id

我试过的：

SELECT *
FROM tblflights
JOIN tblcities
ON tblflights.from_city_id = tblcities.id

结果：

id | from_city_id | to_city_id |   name 
--------------------------------------------
1 |      1       |     2      |  Bahawalpur
2 |      3       |     4      |  Karachi
3 |      2       |     1      |  Multan

但是我想要两个城市的名字(从city_id到city_id(

我尽力了，但没有找到任何解决办法。

我正在使用CodeIgniter

Answer 1

试试这样的方法。

SELECT tf.id, tc1.name from_city_name, tc2.name to_city_name
FROM tblflights tf
JOIN tblcities tc1
ON tf.from_city_id = tc1.id
JOIN tblcities tc2
ON tf.from_city_id = tc2.id;

您可能需要根据表内容删除重复项。