MySQL. Many-to-many table. Select group of users who visited only specific group of another users

Question

I have table 'viewers' with many-to-many relationship

user_id1 - integer - users who make action of visit

user_id2 - integer - users whose profile has visited

And I have to select group of user_id1 (who made action) who visited only specific profiles from user_id2 group

EXAMPLE DATASET

> user_id1 |  user_id2
> 
> 1  | 30
> 
> 1  | 40
> 
> 1  | 50
> 
> 2  | 30
> 
> 2  | 40
> 
> 10 | 40
> 
> 10 | 50
> 
> 11 | 30
> 
> 11 | 40
> 
> 11 | 50
> 
> 12 | 50
> 
> 12 | 60
> 
> 12 | 70
> 
> 13 | 30
> 
> 13 | 40
> 
> 13 | 50
> 
> 13 | 60
> 
> 14 | 90
> 
> 14 | 95
> 
> 14 | 98

I have to select user_id1 who viewed profiles from user_id2 with id's may contains '30' '40' '50' (all at once)

I've tried to made it like, but it's not working

SELECT
t.user_id1,
t.user_id2
FROM viewers t
WHERE 
t.user_id2 in (select distinct t.user_id2 from viewers t WHERE t.user_id2 = 30)
AND  t.user_id2 in (select distinct t.user_id2 from viewers t WHERE t.user_id2 = 40)
AND  t.user_id2 in (select distinct t.user_id2 from viewers t WHERE t.user_id2 = 50)

Answer 1

You can filter the table for the searched ids, group by user_id and set the condition in the having clause:

select user_id1
from viewers
where user_id2 in (30, 40, 50)
group by user_id1
having count(distinct user_id2) = 3;

See the demo.
Results:

| user_id1 |
| -------- |
| 1        |
| 11       |
| 13       |

If you want the user_ids of users who visited only these 3 users then:

select user_id1
from viewers
group by user_id1
having count(distinct user_id2) = 3
   and sum(user_id2 not in (30, 40, 50)) = 0;

See the demo.
Results:

| user_id1 |
| -------- |
| 1        |
| 11       |