CODEWITHSUNDEEP
mysql
timetofly
timetofly

Reputation: 3077

How to select a user from many-to-many table that is in two groups?

I don't actually need the following query, but I woke up with this "theoretical problem" that I'm having trouble figuring out. Say I have three tables: a users table, groups table, and users_groups table that is a many-to-many. So if one user belongs to group 1 and 2, there would be two different rows for each.

Now, assuming that there are many groups, how do I select specifically the users that belong to both group 2 and 3, for example?

I tried something along these lines, but it showed empty:

SELECT * FROM `users_groups` GROUP BY user_id HAVING group_id = 2 AND group_id = 3

I guess that assumes that both groups are in the same row, which obviously won't work. How would I do this?

EDIT: How about both variants: where the user must ONLY be in these two groups, and the user must AT LEAST be in these two groups?

Upvotes: 1

Views: 555

Answers (5)

lc.
lc.

Reputation: 116528

SELECT *
FROM users_groups
GROUP BY user_id
WHERE group_id IN (2,3)
HAVING COUNT(1) = 2

This of course assumes that {user_id, group_id} is unique (and there are no other columns there to add additional rows to the count). Otherwise you could ensure this explicitly:

SELECT *
FROM users_groups
GROUP BY user_id
WHERE group_id IN (2,3)
HAVING COUNT(DISTINCT group_id) = 2

Only in these two groups is slightly more complicated. You can either do:

SELECT *
FROM users_groups g1
GROUP BY user_id
WHERE group_id IN (2,3)
AND NOT EXISTS
(
    SELECT 1
    FROM users_groups AS g2
    WHERE g2.user_id = g1.user_id
    AND group_id NOT IN (2,3)
)
HAVING COUNT(1) = 2

Or,

SELECT *
FROM users_groups g1
GROUP BY user_id
HAVING COUNT(1) = 2
AND SUM(CASE WHEN group_id IN (2,3) THEN 1 ELSE 0 END) = 2

In groups 2 and 3, with more than 2 groups total:

SELECT *
FROM users_groups g1
GROUP BY user_id
HAVING SUM(CASE WHEN group_id IN (2,3) THEN 1 ELSE 0 END) = 2
AND COUNT(1) > 2

Upvotes: 2

Piyush_Chandra
Piyush_Chandra

Reputation: 111

SELECT user_id FROM (select * from users_groups where group_id = 2) grp 1, (select * from users_groups where group_id = 3) grp2 where grp1.user_id = grp2.user_id

Upvotes: 0

Martina
Martina

Reputation: 1672

User must ONLY belong to grp 2 and grp 3:

SELECT *, group_concat(group_id ASC) gui
FROM users_groups
GROUP BY user_id
HAVING gui="2,3"

Upvotes: 0

John Woo
John Woo

Reputation: 263803

The problem is called Relational Division.

SELECT  a.ID, a.Name
FROM    users a
        INNER JOIN users_groups b
            ON a.ID = b.UserID
        INNER JOIN groups c
            ON b.group_ID = c.ID
WHERE   c.Name IN ('grp2', 'grp3')
GROUP   BY a.ID, a.Name
HAVING  COUNT(DISTINCT c.Name) = 2

DISTINCT was used in the following query if a unique constraint on Name isn't enforce for every user, otherwise HAVING COUNT(*) = 2 will suffice.

Upvotes: 2

Salil
Salil

Reputation: 47512

  SELECT *, COUNT(*) FROM `users_groups` 
  WHERE group_id IN (2,3) 
  GROUP BY user_id HAVING COUNT(*) > 1

Upvotes: 1

Related Questions