SQL列值基于从另一个表中的值

Question

我是SQL的新手，无法弄清楚这个简单的东西。我有两个桌子：

+----------+----------+
| Person_1 | Person_2 |
+----------+----------+
|        1 |        2 |
|        1 |        4 |
|        3 |        2 |
+----------+----------+

和

+----+------+
| ID | City |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | A    |
|  4 | A    |
+----+------+

列中的值person_a和person_b参考第二个表中的ID。我想组合这两个表以具有这样的表格：

+----------+----------+---------------+---------------+
| Person_1 | Person_2 | Person_1_City | Person_2_City |
+----------+----------+---------------+---------------+
|        1 |        2 | A             | B             |
|        1 |        4 | A             | A             |
|        3 |        2 | A             | B             |
+----------+----------+---------------+---------------+

我该怎么做？

Answer 1

使用JOIN与城市使用两次：

select p.persion1, p.persion2, 
  c1.city as persion1city,
  c2.city as persion2city
from  person p
left join city c1 on p.Person_1 = c1.id
left join city c2 on p.Person_2 = c2.id