Menu
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?
892
As you may know, it is used to join and combine data from two or more tables into one common data set. In this article, I'm going to discuss special types of joins? in which you combine the same table twice—including joining a table to itself, also known as the self join.
You use self-join to create a result set that joins the rows with the other rows within the same table. Because you cannot refer to the same table more than one in a query, you need to use a table alias to assign the table a different name when you use self-join.
A join to the same table is going to have the same syntax as a join to a different table. If there will always be a value in manager_id, then you can use an INNER JOIN .
Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
