CODEWITHSUNDEEP
mysqlsqlselect
Nystuen
Nystuen

Reputation: 35

How to check which ID that has all the different values of another attribute?

I have a table like this:

+--------+--------+
| userID | itemID |
+--------+--------+
|      1 |      3 |
|      1 |      4 |
|      2 |      3 |
|      2 |      4 |
|      3 |      4 |
+--------+--------+

I am trying to select all the userID's that has all the different itemID's. Meaning, if I were to expand itemID's in the future to have itemID 5 too, the same query would select all the userID's that has itemID 3, 4 and 5.

I've struggled with this problem for several hours now, but not managed to find the general query I am looking for.. I would appreciate all the help I could get!

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Here is one method:

select userid
from t
group by userid
having count(distinct itemid) = (select count(distinct t2.itemid) from t t2);

Upvotes: 1

Related Questions