CODEWITHSUNDEEP
mysqlsqlinner-join
chatu
chatu

Reputation: 325

MySQL joining 4 tables

I'm trying to join 4 tables:

My tables are:


Detailed table info:

  • users: id, name, email, few other info fields
    e.g. 1, chatu rohra, [email protected], few other info fields
  • groups: id, name, email, few other info fields
    e.g. 1, SOME_GROUP_NAME [email protected], few other info fields
  • users_groups: id, user_id, group_id.
    e.g. 1, 1, 1
  • user_favorite_group: id, user_id, user_group_id.
    e.g. 1, 1, 1


    • I'm able to join first three tables (users, groups, users_groups) but not the user_favorite_group. My query is:

    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;

    This gives me:
    users.id, users.name, users.email, groups 1, chatu rohra, [email protected], SOME_GROUP_NAME

    What I would like to see is.. assuming there are more groups in the groups table:
    1, chatu rohra, [email protected], "AND_ANOTHER, SOME_GROUP_NAME, SOME_OTHER_GROUP", "SOME_OTHER_GROUP"

    The 4th field is a list of groups that I'm a member of and my favorite group as the 5th field.

    Any suggestions for name tables, performance would be also great.

    Thanks.

    Upvotes: 1

    Views: 125

    Answers (1)

    Michael Berkowski
    Michael Berkowski

    Reputation: 270609

    The easiest path to join your user_favorite_group table is to add it as an additional LEFT JOIN on the user_id to get the user part of the relationship, but then add an additional INNER JOIN to the groups table to get the group's name. It may not work to try to get the group name from the existing INNER JOIN against groups.

    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

    Looking again at your column names, I may have misunderstood the relationship. if user_favorite_group.user_group_id actually points as a FOREIGN KEY to user_groups.id rather than to groups.id as I've expressed it here, you actually need another additional join:

    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

    Upvotes: 0

    Related Questions