Question

我有一套多个表格(概要):

Brand;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | tinyint(10)  | NO   | PRI | NULL    |       |
| name               | varchar(255) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

Relations;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id1                | tinyint(10)  | NO   | PRI | NULL    |       |
| id2                | tinyint(10)  | NO   | PRI | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

Country;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | tinyint(10)  | NO   | PRI | NULL    |       |
| name               | varchar(255) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

City;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | tinyint(10)  | NO   | PRI | NULL    |       |
| name               | varchar(255) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

每一行都有独特的补贴。品牌表中的每一行都与一个国家和城市有关系。这些表格之间的关系存放在关系表中,因此,每个品牌之间的关系表中有两行。

我试图获得某个品牌的品牌、国名和城市名称。我得以通过个别选择选择选择这些关系,但我的目标是(如果可能的话)在单一询问中这样做。

我能够找到一个关系(国家),但我也想到这个城市:

SELECT Brand.name as brand, Country.name as country
FROM Brand 
LEFT JOIN _relations 
ON Relations.id1 = Brand.id 
INNER JOIN Country 
ON Country.id = Relations.id2 
WHERE Brand.id =  123456 ;

结果:

+-----------+-------------+
| brand     | country     |
+-----------+-------------+
| brandname | countryname |
+-----------+-------------+

我的目标是:

+-----------+-------------+-------------+
| brand     | country     | city        |
+-----------+-------------+-------------+
| brandname | countryname | cityname    |
+-----------+-------------+-------------+

任何人都能够帮助我,或者把我放在正确的方向?

Answer 1

Join Relations再次在不同的标题下提出。

与此类似:

SELECT Brand.name as brand, Country.name as country, City.name as city
FROM Brand 
LEFT JOIN Relations ON Relations.id1 = Brand.id 
INNER JOIN Country ON Country.id = Relations.id2 
LEFT JOIN Relations r2 ON r2.id1 = Brand.id
LEFT JOIN City ON City.id = r2.id2
WHERE Brand.id =  123456 ;

Answer 2

基本上,你们会再次加入关系,然后利用这种关系来进入城市。

SELECT Brand.name as brand, Country.name as country, City.Name as City
FROM Brand 
LEFT JOIN _relations relCountry
ON relCountry.id1 = Brand.id 
INNER JOIN Country 
ON Country.id = relCountry.id2 
LEFT JOIN _relations relCity 
ON relCity.id1 = Brand.id 
INNER JOIN City 
ON City.id = relCity.id2

友情链接