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.

Community · Accepted Answer · 2020-06-15 09:05:18Z

4

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

edited Jun 15, 2020 at 9:05

CommunityBot

1

answered Dec 21, 2017 at 20:27

McNets

24k11 gold badges52 silver badges91 bronze badges

Omg.. So easy and whats important without joins. Thank you very much.

Mykola Borysyuk
– Mykola Borysyuk

2017-12-21 21:58:52 +00:00
Commented Dec 21, 2017 at 21:58
I'm glad to help

McNets
– McNets

2017-12-21 22:05:40 +00:00
Commented Dec 21, 2017 at 22:05
Btw..One more small question..This query still be effective on huge datasets like 10 millions records and so on? Thank you

Mykola Borysyuk
– Mykola Borysyuk

2017-12-21 22:06:27 +00:00
Commented Dec 21, 2017 at 22:06
@MykolaBorysyuk I think so, but have one index on interest?

McNets
– McNets

2017-12-21 22:07:17 +00:00
Commented Dec 21, 2017 at 22:07
"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 ;)

ypercubeᵀᴹ
– ypercubeᵀᴹ

2017-12-21 23:04:51 +00:00
Commented Dec 21, 2017 at 23:04

| Show 1 more comment

Stack Exchange Network

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

1 Answer 1

Your Answer

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions