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

MySQL

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.

Best Answer

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

Related Question