Select common Interactions mysql [closed]

I have this 3 tables:

[user]
id | name  |  level
10 | John  |   1
11 | Josh  |   2
12 | Mary  |   3

[level_interaction]
level | interact_with
  1   |     1
  1   |     2

  2   |     1
  2   |     2
  2   |     3

  3   |     2
  3   |     3

room_id | user_id
1       |  10
1       |  11

2       |  11
2       |  12

3       | 11

what I want is select which level can interact with the room.

If I check for room 1, the answer should be level 1 and 2 (because John is level 1 and he is only able to interact with level 2) even though Josh as level 2 can interact with level 3.
If I check for room 3, because only Josh is there, the answer should be 1 2 and 3.
If I select the room 2, the answer should be level 2 and 3.

Basically what I want is a way to select common interaction level for members on the room!

Check the table/dataset on: https://paiza.io/projects/NKkAQUg9CuB_46rXZdEVEA?language=mysql

Answer

Join the tables and group by li.interact_with with a condition in the HAVING clause:

select li.interact_with
from room r
inner join user u on u.id = r.user_id
inner join level_interaction li on li.level = u.level
where r.room_id = 1
group by li.interact_with
having count(*) = (select count(user_id) from room where room_id = 1);

See the demo.
Results (for room_id = 1):

> | interact_with |
> | ------------: |
> |             1 |
> |             2 |

Advertisement

Answer