mysql - 在mysql中填充/加入多对多关系表的多个表

Question

这个问题之前应该有人问过，但我发现很难找到。我有表 users、allergies、dietaries。用户可以有多种过敏症和多种饮食。为此，我有 user_allergies、user_dietaries 表，它存储两个表的外键。

现在我想显示用户列表及其相应的过敏、饮食名称。我试过像这样使用左连接 -->

SELECT
    users.user_id as 'id',
    IFNULL(CONCAT(users.first_name,' ',users.last_name),'') as 'name',
    IFNULL(GROUP_CONCAT(allergies.title),'') as 'allergies'
FROM (users
    LEFT JOIN user_allergies
        ON users.user_id = user_allergies.user_id
    LEFT JOIN allergies
        ON user_allergies.allergies_id = allergies.allergies_id
      )
GROUP BY users.user_id;

它的响应完全符合我的要求-->

7 | Khabib Nurmagamedov | Milk,Corn
8 | Conor Mcgregor      | Milk,Corn

现在我希望以同样的方式获取dietaries。所以我为 dietaries 添加了类似的 Left Join -->

SELECT
    users.user_id as 'id',
    IFNULL(CONCAT(users.first_name,' ',users.last_name),'') as 'name',
    IFNULL(GROUP_CONCAT(allergies.title),'') as 'allergies',
    IFNULL(GROUP_CONCAT(dietaries.title),'') as 'dietaries'
FROM (users
    LEFT JOIN user_allergies
        ON users.user_id = user_allergies.user_id
    LEFT JOIN allergies
        ON user_allergies.allergies_id = allergies.allergies_id
    LEFT JOIN user_dietaries
        ON users.user_id = user_dietaries.user_id
    LEFT JOIN dietaries
        ON user_allergies.allergies_id = dietaries.dietaries_id
      )  
GROUP BY users.user_id;

但现在我得到的输出 -->

7 | Khabib Nurmagamedov | Milk,Corn,Eggs,Meat | Milk,Corn,Eggs,Meat
8 | Conor Mcgregor      | Milk,Corn,Eggs,Meat | Milk,Corn,Eggs,Meat

应该是 -->

7 | Khabib Nurmagamedov | Milk,Corn | Eggs,Meat
8 | Conor Mcgregor      | Milk,Corn | Eggs,Meat

我在这里挣扎。我应该怎么办？提前致谢。

编辑:响应是 -->

7 | Khabib Nurmagamedov | Milk,Corn,Milk,Corn | Milk,Milk,Corn,Corn
8 | Conor Mcgregor      | Milk,Eggs,Milk,Eggs | Milk,Milk,Corn,Corn

我认为两个表数据都被连接起来了，但显然结果出现了两次。结果需要-->

7 | Khabib Nurmagamedov | Milk,Corn | Milk,Corn
8 | Conor Mcgregor      | Milk,Eggs | Milk,Corn

Answer 1

您的问题是您希望 GROUP_CONCAT 位于 2 个单独的列中，但它不能那样工作。

您必须从用户表中加入您的 group_concat 结果 2 次:

SELECT u.user_id as 'id',
             IFNULL(CONCAT(u.first_name,' ',u.last_name),'') as 'name',
             ua.allergies_c as 'allergies',
             ud.dietaries_c as 'dietaries'
FROM users u
LEFT JOIN
    (
        SELECT
            users.user_id ,
            IFNULL(GROUP_CONCAT(allergies.title),'') as allergies_c
        FROM (users
            LEFT JOIN user_allergies
                ON users.user_id = user_allergies.user_id
            LEFT JOIN allergies
                ON user_allergies.allergies_id = allergies.allergies_id
              )
        GROUP BY users.user_id
    ) ua ON u.user_id=ua.user_id
LEFT JOIN
    (
        SELECT
            users.user_id,
            IFNULL(GROUP_CONCAT(dietaries.title),'') as dietaries_c
        FROM (users
            LEFT JOIN user_dietaries
                ON users.user_id = user_dietaries.user_id
            LEFT JOIN dietaries
                ON user_dietaries.dietaries_id = dietaries.dietaries_id
              )
        GROUP BY users.user_id
    ) ud ON u.user_id=ud.user_id

我进行了一些批量替换以加快速度，您可能需要修复一些问题，但想法就在那里。