2

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.

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

6
  • Omg.. So easy and whats important without joins. Thank you very much. Dec 21 '17 at 21:58
  • I'm glad to help
    – McNets
    Dec 21 '17 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 Dec 21 '17 at 22:06
  • @MykolaBorysyuk I think so, but have one index on interest?
    – McNets
    Dec 21 '17 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 ;) Dec 21 '17 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.