SQL query. Many to many relation with AND condition

Question

SQL

Given:

table users:
id
1
2
3

table info:
id
1
2
3

table users_info:
id | user_id | info_id
1        1          1
2        1          2
3        1          3
4        2          2
5        2          3
6        3          1
7        3          2

Required:
make a selection of users from the database who simultaneously have info.id = 1 AND info.id = 2 AND info.id = 3

<b>Результат должен быть:</b>
table result:
user_id
1

Answer 1

select user_id, group_concat(distinct info_id order by info_id asc) as _i
from user_info where info_id between 1 and 3 group by user_id
having _i = '1,2,3';

Unlike the option proposed above, the uniqueness of the user_id, info_id binding is not important for this request.

Answer 2

I will offer a slightly modified version of @kazmiruk
"3" is the number of elements in the IN operator.

Answer 3

More general version:

select user_id from users_info as info
group by user_id
having count(case when info.id = 1 then 1 end) > 0 
   and count(case when info.id = 2 then 1 end) > 0 
   and count(case when info.id = 3 then 1 end) > 0;

You can add other conditions there.

Answer 4

I wrote a solution to this problem in a previous question. Check it out.