如何在单个mysql查询中从多个关系表中多个连接值 [英] How to multiple concatenate values from multiple relation tables in a single mysql query

问题描述

我的旅行报价"项目有一个大问题，可以工作99％，但不能100％.

I have a big issue for my "traveling offer" project, working 99% OK, but not 100%.

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

I have main table with offers, where each offer can have set multiple department cities as well as multiple destination cities (this is reduced sample with reduced columns).

例如，我提供一些来自英国的旅行，而部门城市可以来自伦敦，利兹和曼彻斯特.目的地城市是布拉格，布拉迪斯拉发，布达佩斯和贝尔格莱德.

For example, I'm offering some travels from England, where department cities can be from London, Leeds and Manchester. Destination cities are Prague, Bratislava, Budapest and Belgrade.

要约1被设置为伦敦或利兹的部门城市，目的地是布拉格和布达佩斯. 优惠2设置为伦敦部门城市，目的地为布拉迪斯拉发和贝尔格莱德. 优惠3设置为部门城市曼彻斯特或利兹，目的地是布拉格.

Offer 1 is set to department cities London or Leeds, and destinations are Prague and Budapest. Offer 2 is set to department city London, and destinations are Bratislava and Belgrade. Offer 3 is set to department city Manchester or Leeds, and destination is Prague.

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

现在的关系表和城市名称表

Now relation tables and city name tables

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结果，我希望为部门城市和目标城市的每个商品提供连接值bot

As SQL result I expect for each offer concatenated values bot for department cities and destination cities

如果我使用以下sql搜索所有内容，则会得到好的结果:

If I search all with following sql I got OK result:

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语句的末尾:

And I need results like this whatever WHERE clause is. But, whenever I put where clause, I loose one of results in concatenation. For example, I search for all offers with Prague as a destination. If I add to the end of the sql statement:

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

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

If you can notice, there is no Budapest in offer 1. What to do to get complete concatenation string... Not that WHERE clause can be more complex, i.e. to search for department city or any other parameter.

我们将不胜感激:)

推荐答案

您需要将rel_destinations与其他联接一起使用，才能将布拉格作为目的地.将其与您的原始查询联系起来.

You need to use a different join with rel_destinations to get the offers with Prague as a destination. Join this with your original query.

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

演示

这篇关于如何在单个mysql查询中从多个关系表中多个连接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！