MySQL:多重选择,多重连接?
问题描述:
我有一组多个表(剥离概述)的:MySQL:多重选择,多重连接?
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 |
+-----------+-------------+-------------+
任何人都可以帮助我或指出我在正确的方向吗?
答
加入关系表再次在不同的别名下。
事情是这样的:
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';
答
基本上你再次加入到关系,然后用它来获得市,所以像
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
太好了,谢谢你,这是一个完美的解决方案。但是我遇到了一个新问题。如果一个城市不存在一个品牌会怎么样?如果在关系表中找不到城市标识,它现在会返回0行。有没有办法可以忽略连接,并返回null或者City字段的自定义值?而不是没有结果,我想检索品牌名称,国家名称和一个空的城市领域。 – Tod 2012-01-14 13:18:33
外部连接而不是内部连接。 – 2012-01-15 18:08:15