MySQL Select rows with same id but different multiple values in another colum

Question

Maybe my question will be silly, but i'm new to Mysql

I have a table with user_id and some interest.

+------+------+
|userID|Inter |
+------+------+
|1 |sport |
+------+------+
|2 |it |
+------+------+
|3 |game |
+------+------+
|1 |it |
+------+------+
|1 |game |
+------+------+
|3 |it |
+------+------+
|3 |sport |
+------+------+

Amount of interests can be huge(let say 20 or whatever)

Now i need to find all userId's that have interests it, game, sport;

Of course simple AND wont work as because of different rows.

So my question will be how to do it, so the output will be

+------+
|userID|
+------+
|1 |
+------+
|3 |
+------+

Thank you.

Answer 1

You can get it by using GROUP BY and COUNT(*)

create table users (user_id int, interest varchar(20));

insert into users values(1, 'sport');
insert into users values(2, 'it');
insert into users values(3, 'game');
insert into users values(1, 'it');
insert into users values(1, 'game');
insert into users values(3, 'it');
insert into users values(3, 'sport');

SELECT user_id
FROM users
WHERE interest IN ('game', 'it', 'sport')
GROUP BY user_id
HAVING count(*) = 3;

| user_id |
| ------: |
| 1 |
| 3 |

dbfiddle here

• Mykola Borysyuk: Omg.. So easy and whats important without joins. Thank you very much.
• McNets: I'm glad to help
• Mykola Borysyuk: Btw..One more small question..This query still be effective on huge datasets like 10 millions records and so on? Thank you
• McNets: @MykolaBorysyuk I think so, but have one index on interest?
• ypercubeᵀᴹ: "whats important without joins": There are several solution for this kind of problems. Many of them use joins - and are often faster than the GROUP BY - HAVING method ;)