CODEWITHSUNDEEP
mysql
Klipp Ohei
Klipp Ohei

Reputation: 385

Multiple left joins (with multiple tables) with a condition

I want to have a group list but only of those groups where the user is a member. So I need multiple left joins (because I have multiple tables for groups, users, games...) and a condition because the I want only the groups where the user is member listed.

Here are my tables:

table users - PRIMARY KEY (user_id)
+---------+----------+-----------+
| user_id | username | realname  |
+---------+----------+-----------+
|       1 | peterpan | Peter Pan |
|       2 | bobfred  | Bod Fred  |
|       3 | sallybe  | Sally Be  |
|       6 | petersep | Peter Sep |
+---------+----------+-----------+

table users_groups - PRIMARY KEY (user_id, group_id)
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1       | 1        |
| 1       | 2        |
| 2       | 1        |
| 2       | 2        |
| 3       | 6        |
| 3       | 9        |
| 6       | 6        |
| 6       | 9        |
+---------+----------+

table game - PRIMARY KEY (id)
+----+-------+
| id | game  |
+----+-------+
| 1  | Game1 |
| 2  | Game2 |
| 6  | Game6 |
| 9  | Game9 |
+----+-------+

table groups - PRIMARY KEY(group_id)
+----------+--------------+---------------+
| group_id | group_name   | group_desc    |
+----------+--------------+---------------+
| 1        | Groupname1   | Description1  |
| 2        | Groupname2   | Description2  |
+----------+--------------+---------------+

table group_game - PRIMARY KEY(group_id, game_id)
+----------+----------+
| group_id | game_id  |
+----------+----------+
| 1        | 1        |
| 1        | 2        |
| 2        | 6        |
| 2        | 9        |
+----------+----------+

And now I want to display something like this (only the groups where Peter Pan is member, because Peter Pan is actually logged in):

+----+------------+--------------+---------------------+--------------+
| id | group name | group desc   | group members       | group games  |
+----+------------+--------------+---------------------+--------------+
|  1 | GroupName1 | Description1 | Peter Pan, Bob Fred | Game1, Game2 |
+----+------------+--------------+---------------------+--------------+

This is my query (I use prepared statements and the ? would be the userid of the user)

SELECT
    g.group_name,
    g.group_id,
    g.group_desc,
    GROUP_CONCAT(DISTINCT u.realname SEPARATOR ', ') AS users,
    GROUP_CONCAT(DISTINCT ga.game SEPARATOR ', ') AS games
FROM groups g
LEFT JOIN users_groups ug1
    ON g.group_id=ug1.group_id
LEFT JOIN users u
    ON ug1.user_id=u.user_id AND u.user_id = ?
LEFT JOIN group_game gg
    ON g.group_id=gg.group_id
LEFT JOIN game ga
    ON gg.game_id=ga.id
GROUP BY g.group_name

My query doesn't work because it lists all groups and not just the groups where the actual user is member. How can I solve this?

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want the users in the groups that have the user as a member, then remove the condition on the user in the left join and add:

HAVING MAX(u.user_id = ?) > 0

This returns only the groups with the given member.

Note: you probably don't need LEFT JOINs either. The tales should have proper foreign key relationships, and need to have at least one member (the user in question).

Upvotes: 2

Related Questions