mysql 多连接插入,MySQL在同一个表上加入多个连接？

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

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.

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

解决方案

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:

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