CODEWITHSUNDEEP
mysqlsqldatabaserelational-division
Roman Bekkiev
Roman Bekkiev

Reputation: 3118

SQL: how to get rows containing only certain ids?

I've got the following table:

+--------+--------+
|  group |  user  |
+--------+--------+
|      1 |      1 |
|      1 |      2 |
|      2 |      1 |
|      2 |      2 |
|      2 |      3 |
+--------+--------+

I need to select group, containing both user 1 and 2 and only 1 and 2 (not 3 or 42).

I tried 

SELECT `group` FROM `myTable` 
WHERE `user` = 1 OR `user` = 2 
GROUP BY `group`;

But that of course gives me groups 1 and 2 while group 2 contains also user 3.

Upvotes: 3

Views: 70

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You may try this:

SELECT `group` 
FROM myTable
GROUP BY `group`
HAVING GROUP_CONCAT(DISTINCT `user` ORDER BY `user`) = '1,2';

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453395

One way

SELECT `group` 
FROM myTable
GROUP BY `group`
HAVING GROUP_CONCAT(DISTINCT `user` ORDER BY `user`) = '1,2';

SQL Fiddle

Upvotes: 2

Related Questions