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

Question

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?

lc. · Accepted Answer · 2013-03-21T12:33:07.517

2

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

edited Mar 21 '13 at 12:33

answered Mar 21 '13 at 11:43

lc.

113,939
20
158
187

you should ideally have count > 1 as user can belong to more than 2 groups. – Harry Joy Mar 21 '13 at 11:45
1

@HarryJoy The OP specifically wanted users that belong to both group 2 and 3, so `= 2` is correct here (i.e. 2 of 2 groups). – lc. Mar 21 '13 at 11:46
What does `HAVING COUNT(1)` mean? What does it mean to count 1? – timetofly Mar 21 '13 at 11:49
@user371699 Same as `COUNT(*)` really. Some engines will actually hit the disk for `COUNT(*)` and not for `COUNT(1)`. See also http://stackoverflow.com/questions/1221559/count-vs-count1 and http://stackoverflow.com/questions/5179969/what-is-better-in-mysql-count-or-count1 which says that in MySQL, they are the same. – lc. Mar 21 '13 at 11:53
Thanks, didn't know that. Now, if I may, I wish to sweeten the deal a little =) What would the query look like if I wanted to select the users that are in both groups 2 and 3, but also in 3 or 4 groups total? Or maybe just more than 2 groups total (to make it easier)? – timetofly Mar 21 '13 at 11:59
@user371699 Check the last query I added now. You can of course change it to `AND COUNT(1) = 3` for exactly 3 groups – lc. Mar 21 '13 at 12:02
Can you please explains what `SUM(CASE WHEN group_id IN (2,3) THEN 1 ELSE 0 END)` means? To me, it seems that if the group_id belongs to either 2 or 3, it SUMS just 1. How can you sum a single number? – timetofly Mar 21 '13 at 12:20
@user371699 It's for *each row*. So for all rows, add 1 if the `group_id` is 2 or 3 for that row, otherwise add 0. The result is a conditional count - a count of rows which have `group_id` equal to 2 or 3. – lc. Mar 21 '13 at 12:22
I see what you are saying, but if that is the case, then wouldn't the second to last query need to be `HAVING SUM(CASE WHEN group_id IN (2,3) THEN 1 ELSE 0 END) = 2`? It seems that you're just counting, and there's no condition to specify what the result of the count should be. – timetofly Mar 21 '13 at 12:26
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26638/discussion-between-lc-and-user371699) – lc. Mar 21 '13 at 12:28

score 2 · Answer 2 · answered Mar 21 '13 at 11:45

2

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.

SQL of Relational Division

answered Mar 21 '13 at 11:45

John Woo

258,903
69
498
492

1

Thanks for elaborately showing the complete query with all the joins =) – timetofly Mar 21 '13 at 12:00
Would there be any difference performance-wise if I were to count by b.group_ID or c.ID instead of c.Name? – timetofly Mar 21 '13 at 12:03
@user371699 I think it should speed up if `ID` have index keys (*primary*, etc..) – John Woo Mar 21 '13 at 12:05
1

So if all three had an index then it wouldn't matter? – timetofly Mar 21 '13 at 12:07

score 1 · Answer 3 · answered Mar 21 '13 at 11:43

1

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

answered Mar 21 '13 at 11:43

Salil

46,566
21
122
156

score 0 · Answer 4 · answered Mar 21 '13 at 11:50

0

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"

answered Mar 21 '13 at 11:50

Martina

1,634
1
10
6

Piyush_Chandra · Answer 5 · 2013-03-21T12:14:27.037

0

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

edited Mar 21 '13 at 12:14

answered Mar 21 '13 at 12:07

Piyush_Chandra

111
1
9

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

5 Answers5