mysql - 如何在单个 mysql 查询中多重连接来自多个关系表的值

Question

我的“旅行报价”项目有一个大问题，工作 99% OK，但不是 100%。

我有一个包含优惠的主表，其中每个优惠可以设置多个部门城市以及多个目的地城市(这是减少列的减少样本)。

例如，我提供一些从英格兰出发的旅行，其中的省会城市可以是伦敦、利兹和曼彻斯特。目的地城市为布拉格、布拉迪斯拉发、布达佩斯和贝尔格莱德。

优惠 1 设置为伦敦或利兹的部门城市，目的地为布拉格和布达佩斯。Offer 2 定为百货城市伦敦，目的地为布拉迪斯拉发和贝尔格莱德。Offer 3 设置百货城市曼彻斯特或利兹，目的地为布拉格。

Table offers
----------------------------
id  title            price
----------------------------
1   Offer 1 title    300 Eur
2   Offer 2 title    250 Eur
3   Offer 3 title    350 Eur

现在关系表和城市名表

Table departments
----------------------------
id  name
----------------------------
1   London
2   Leeds
3   Manchester


relation Table rel_departments
------------------------
offer_id  rel_id
------------------------
1         1
1         2
2         1
3         2
3         3


Table destinations
----------------------------
id  name
----------------------------
1   Prague
2   Bratislava
3   Budapest
4   Belgrade


relation Table rel_destinations
------------------------
offer_id  rel_id
------------------------
1         1
1         3
2         2
2         4
3         1

作为 SQL 结果，我希望每个报价都为部门城市和目的地城市提供串联值机器人

如果我使用以下 sql 搜索所有结果，我会得到 OK 结果:

SELECT offers.*, 
  GROUP_CONCAT(DISTINCT DEPC.name SEPARATOR ', ') AS depCities,
  GROUP_CONCAT(DISTINCT DESTC.name SEPARATOR ', ') AS destCities
FROM offers
INNER JOIN `rel_departments` ON (`rel_departments`.`offer_id` = `offers`.`id`)
INNER JOIN `departments` as DEPC ON (DEPC.`id` = `rel_departments`.`rel_id`)
INNER JOIN `rel_destinations` ON (`rel_destinations`.`offer_id` = `offers`.`id`)
INNER JOIN `destinations` as DESTC ON (DESTC.`id` = `rel_destinations`.`rel_id`)
GROUP BY offers.id

结果还可以:

---------------------------------------------------------------------
id  title            price    depCities          destCities
---------------------------------------------------------------------
1   Offer 1 title    300 Eur  London, Leeds      Prague, Budapest
2   Offer 2 title    250 Eur  London             Bratislava, Belgrade
3   Offer 3 title    350 Eur  Leeds, Manchester  Prague

无论 WHERE 子句是什么，我都需要这样的结果。但是，每当我放置 where 子句时，我都会丢失一个连接结果。例如，我搜索以布拉格为目的地的所有报价。如果我在 sql 语句的末尾添加:

where rel_destinations.rel_id=1

结果如下:

---------------------------------------------------------------------
id  title            price    depCities          destCities
---------------------------------------------------------------------
1   Offer 1 title    300 Eur  London, Leeds      Prague
3   Offer 3 title    350 Eur  Leeds, Manchester  Prague

如果您注意到，offer 1 中没有布达佩斯。如何获得完整的串联字符串...并不是说 WHERE 子句可以更复杂，即搜索部门城市或任何其他参数。

感谢任何帮助:)

Answer 1

您需要使用与 rel_destinations 的不同连接来获取以布拉格为目的地的报价。将此与您的原始查询结合起来。

SELECT offers.*, 
  GROUP_CONCAT(DISTINCT DEPC.name SEPARATOR ', ') AS depCities,
  GROUP_CONCAT(DISTINCT DESTC.name SEPARATOR ', ') AS destCities
FROM offers
INNER JOIN `rel_departments` ON (`rel_departments`.`offer_id` = `offers`.`id`)
INNER JOIN `departments` as DEPC ON (DEPC.`id` = `rel_departments`.`rel_id`)
INNER JOIN `rel_destinations` ON (`rel_destinations`.`offer_id` = `offers`.`id`)
INNER JOIN `destinations` as DESTC ON (DESTC.`id` = `rel_destinations`.`rel_id`)
INNER JOIN rel_destinations AS d1 ON d1.offer_id = offers.id
WHERE d1.rel_id = 1
GROUP BY offers.id

DEMO