Prompt SQL query to select users by their options

Question

MySQL

There are users and their options (for example, lives in Moscow or St. Petersburg, uses a mobile, tablet or laptop to access the network ..., young, middle or older, etc.).

Thus, each user has several options



. Now I make a selection by options and I need to return the corresponding users. For example, I choose that a person lives in Moscow and has a tablet. There is a link table - user-option.



How to build a SQL query to solve a problem?

Answer 1

Maybe so:

SELECT userid FROM user_options WHERE optionid IN (2, 3) GROUP BY userid HAVING COUNT(*) = 2

Or like this:

SELECT o1.userid FROM user_options o1
INNER JOIN user_options o2 ON o1.userid = o2.userid AND o2.optionid = 3
WHERE o1.optionid = 2

Answer 2

While it comes to mind to make aliases

SELECT 
    a.userid
FROM 
    user_options a, 
    user_options b
WHERE a.userid = b.userid
    AND a.optionid=2
    AND b.optionid=3
GROUP BY a.userid

However, the solution is not flexible. If I select 10 options when searching, the query will take a long time to run as the tables grow in size. Maybe there is a more flexible solution?

Answer 3

And so as not to be too boring here, I will offer another solution for MySQL:

SELECT user_id FROM user_option GROUP BY user_id HAVING GROUP_CONCAT(DISTINCT option_id ORDER BY user_id ASC SEPARATOR ',') = '1,2,3,4,5';

Answer 4

SELECT user FROM table WHERE option=someOption INTERSECT SELECT user FROM table WHERE option2=someOption2 INTERSECT etc…