MySQL JOIN在同一表上有多个联接? [英] MySQL JOIN Multiple Joins on the same 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

我在这里想要做的是将Country1(id)链接到Country1(名称)并仅显示名称. 此代码示例仅在Country1，Territory1，City1与Country2，Territory2，City2相同的情况下有效

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

我会想到我的问题是我如何进行JOIN.我是SQL方面的新手.我已经在互联网上阅读了JOINS(Google搜索并阅读了前几篇教程)，但是在这种情况下，我所读到的内容没有任何帮助.

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?

推荐答案

您需要为每个国家/地区/城市使用2个单独的联接.以下是基本语法，您可能需要稍作更改，因为我没有将其通过解析器:

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

这篇关于MySQL JOIN在同一表上有多个联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！