Geek Answers Handbook

MySQL JOIN Multiple joins in one table?

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 only works if Country1, Territory1, City1 match Country2, Territory2, City2

I would like my problem to be how I make my JOIN. I am new to the SQL side of things. I read JOINS on the Internet (googling and reading the first few tutorials), however, none of what I read helped in this case.

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

+7
source share
3 answers

you need 2 separate associations for each country / city / territory. below is the basic syntax, you may need to modify it a bit, since I did not pass it through the 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
+14
source

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
+6
source 
 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
+4
source

More articles:


All Articles