MySQL JOIN 同一个表上的多个连接？

MySQL JOIN Multiple Joins on the same table?

提问by rlemon

SELECT people.first_name AS "First Name", people.last_name AS "Last Name", countries.name AS "Country1", territories.name AS "Territory1", cities.name AS "City1", countries.name AS "Country2", territories.name AS "Territory2", cities.name AS "City2"
FROM adb_people AS people
JOIN root_cities AS cities ON people.city1 = cities.id
AND people.city2 = cities.id
JOIN root_territories AS territories ON people.prov_state1 = territories.id
AND people.prov_state2 = territories.id
JOIN root_countries AS countries ON people.country1 = countries.id

What i'm trying to do here is link Country1 (id) to Country1 (name) and display only the name. This code example works only if Country1,Territory1,City1 are the same as Country2,Territory2,City2

我在这里尝试做的是将 Country1 (id) 链接到 Country1 (name) 并仅显示名称。此代码示例仅在 Country1,Territory1,City1 与 Country2,Territory2,City2 相同时才有效

I would image my issue is how i'm doing my JOIN. I'm new to the SQL side of things. I have read up on JOINS on the internet (google search and read the first few tutorials) however nothing I have read has been any help in this case.

我想我的问题是我如何做我的 JOIN。我是 SQL 方面的新手。我已经阅读了互联网上的 JOINS（谷歌搜索并阅读了前几个教程），但是在这种情况下我读到的任何内容都没有任何帮助。

I would really appreciate any help with what i'm doing wrong here. Maybe a nudge in the right direction?

我真的很感激我在这里做错了什么。也许朝着正确的方向轻推？

回答by Derek Kromm

you need 2 separate joins for each country/city/territory. below is the basic syntax, you might need to change it slightly as i haven't put it through a parser:

每个国家/城市/地区需要 2 个单独的连接。以下是基本语法，您可能需要稍微更改它，因为我还没有通过解析器将其放入：

SELECT people.first_name AS "First Name", people.last_name AS "Last Name", 
countries1.name AS "Country1", territories1.name AS "Territory1", cities1.name AS "City1", 
countries2.name AS "Country2", territories2.name AS "Territory2", cities2.name AS "City2"
FROM adb_people AS people
JOIN root_cities AS cities1 ON people.city1 = cities1.id
  AND people.city2 = cities1.id
JOIN root_territories AS territories1 ON people.prov_state1 = territories1.id
  AND people.prov_state2 = territories1.id
JOIN root_countries AS countries1 ON people.country1 = countries1.id
JOIN root_cities AS cities2 ON people.city2 = cities2.id
  AND people.city2 = cities2.id
JOIN root_territories AS territories2 ON people.prov_state2 = territories2.id
  AND people.prov_state2 = territories2.id
JOIN root_countries AS countries2 ON people.country2 = countries2.id

回答by Cory House

This will do the trick.

这将解决问题。

SELECT people.first_name AS "First Name", people.last_name AS "Last Name", countries.name AS "Country1", territories.name AS "Territory1", cities.name AS "City1", countries2.name AS "Country2", territories2.name AS "Territory2", cities2.name AS "City2"
FROM adb_people AS people
JOIN root_cities AS cities ON people.city1 = cities.id
JOIN root_cities AS cities2 ON people.city2 = cities.id
JOIN root_territories AS territories ON people.prov_state1 = territories.id
JOIN root_territories AS territories2 ON people.prov_state2 = territories.id
JOIN root_countries AS countries ON people.country1 = countries.id
JOIN root_countries AS countries2 ON people.country2 = countries.id

回答by Bob Vale

SELECT 
  people.first_name AS "First Name", 
  people.last_name AS "Last Name",
  countries.name AS "Country1",
  territories.name AS "Territory1",
  cities.name AS "City1",
  countries2.name AS "Country2",
  territories2.name AS "Territory2", 
  cities2.name AS "City2"
FROM 
   adb_people AS people
   JOIN root_cities AS cities ON people.city1 = cities.id
   jOIN root_cities AS cities2 people.city2 = cities2.id
   JOIN root_territories AS territories ON people.prov_state1 = territories.id
   JOIN root_territories AS territories2 ON people.prov_state2 = territories2.id
   JOIN root_countries AS countries ON people.country1 = countries.id
   JOIN root_countries AS countries2 ON people.country2 = countries2.id