MySQL:多重选择，多重连接？

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    |       |
+--------------------+--------------+------+-----+---------+-------+

每一行都有一个唯一的 ID。品牌表中的每一行都与国家和城市相关。这些表之间的关系存储在关系表中，因此每个品牌行在关系表中有两行。

我正在尝试获取给定品牌 ID 的品牌名称、国家/地区名称和城市名称。我可以通过单独的选择来选择这些关系，但我的目标是(如果可能的话)在单个查询中完成。

我能够得到其中一个关系(国家)，但我也想得到城市:

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

在不同的别名下再次加入 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';