MySQL JOIN Multiple Joins on the same table?

Question

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?

score 28 · Accepted Answer · answered Aug 02 '11 at 12:56

28

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

answered Aug 02 '11 at 12:56

Derek

21,828
7
53
61

Duh, Why didn't I try that to begin with. I will implement it now and let you know how it goes. Thankyou! – rlemon Aug 02 '11 at 12:57
Doesn't this answer still have the problem that it only works if city1=city2? – Bob Vale Aug 02 '11 at 12:59
It shouldn't, no. I did just edit the query because i forgot to change my table aliases in the select portion from the copy/paste, if that is what you're referring to? – Derek Aug 02 '11 at 13:01
hehe yes the sql statement above is wrong, but the answer given is right. NOT using the example but altering my SQL statement to JOIN twice with two identifiers was the answer! – rlemon Aug 02 '11 at 13:10

score 9 · Answer 2 · answered Aug 02 '11 at 12:57

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

score 8 · Answer 3 · answered Aug 02 '11 at 13:03

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

MySQL JOIN Multiple Joins on the same table?

3 Answers3

Linked