MySQL连接4个表

Question

我正在尝试连接 4 个表:

我的表是:

• users(用户信息)，
• 群组(群组信息)，
• users_groups(用户组信息:多对多，一个用户可以是多个组的成员，一个组可以有多个成员)，
• user_favorite_group(用户最喜欢的组)。

详细表格信息:

用户:id、姓名、电子邮件、一些其他信息字段
例如1，chatu rohra，chaturanga@example.com，其他一些信息字段

群组:ID、姓名、电子邮件和一些其他信息字段
例如1，SOME_GROUP_NAME some@email.com，其他一些信息字段

users_groups:id、user_id、group_id。
例如1, 1, 1

user_favorite_group:id、user_id、user_group_id。
例如1, 1, 1

我可以加入前三个表(users、groups、users_groups)，但不能加入 user_favorite_group。我的查询是:

SELECT
users.*,
GROUP_CONCAT(DISTINCT groups.name ORDER BY groups.name ASC SEPARATOR ', ') as groups
FROM users_groups
    INNER JOIN users ON users.id = users_groups.user_id
    INNER JOIN groups ON groups.id = users_organizations.organization_id
    WHERE users.id = 1;

这给了我:
users.id, users.name, users.email, groups1, chatu rohra, some@email.com, SOME_GROUP_NAME

我想看到的是..假设组表中有更多组:
1, chatu rohra, some@email.com, "AND_ANOTHER, SOME_GROUP_NAME, SOME_OTHER_GROUP", "SOME_OTHER_GROUP"

第 4 个字段是我所属的组的列表，第 5 个字段是我最喜欢的组。

对名称表有任何建议，性能也会很好。

谢谢。

Answer 1

加入 user_favorite_group 表的最简单方法是将其添加为 user_id 上的附加 LEFT JOIN 以获取 user 关系的一部分，然后向 groups 表添加一个额外的 INNER JOIN 以获取组的名称。尝试从针对 groups 的现有 INNER JOIN 获取组名可能无效。

SELECT
  users.*,
  GROUP_CONCAT(DISTINCT groups.name ORDER BY groups.name ASC SEPARATOR ', ') as groups,
  gfav.name AS favorite_group
FROM users_groups
    INNER JOIN users ON users.id = users_groups.user_id
    INNER JOIN groups ON groups.id = users_organizations.organization_id
    -- Join user to user_favorite_group
    -- Using LEFT JOIN in case the user has no favorite, it will return a NULL.
    LEFT JOIN `user_favorite_group` ON users.id = user_favorite_group.user_id
    -- Join that to `groups` to get its group name
    INNER JOIN groups gfav ON user_favorite_group.user_group_id
WHERE users.id = 1
-- MySQL will allow only users.id in the GROUP BY (or none at all)
-- but know that this isn't entirely portable.
GROUP BY users.id

再看你的列名，我可能理解错了关系。如果 user_favorite_group.user_group_id 实际上作为 FOREIGN KEY 指向 user_groups.id 而不是像我一样指向 groups.id在这里表达了它，你实际上需要另一个额外的连接:

FROM users_groups
    INNER JOIN users ON users.id = users_groups.user_id
    INNER JOIN groups ON groups.id = users_organizations.organization_id
    -- Join user to user_favorite_group
    -- Using LEFT JOIN in case the user has no favorite, it will return a NULL.
    LEFT JOIN `user_favorite_group` ON users.id = user_favorite_group.user_id
    -- Join through `user_groups` to get the relationship
    INNER JOIN user_groups ugfav ON user_favorite_group.user_group_id = ugfav.id
    -- Join ultimlately back to `groups` to get the group name.
    INNER JOIN groups gfav ON ugfav.group_id = .gfav.id