How to select list of rooms containing all items from a list?

I have a table items_in_room that links which item is in which room (columns are room_id and item_id). Is there a way to select all rooms that contain all items from the list?

I’m aware that selecting room which contains one item with id x would be:

select room_id 
  from items_in_room
 where item_id = x

Is there a way to write a select query if for example we need room that contains items with ids x,y,z?

Answer

If you have a list, you can do:

select room_id
from items_in_room
where item_id in ( . . . )  -- list of items here
group by room_id
having count(*) = <n>;  -- number of items in list

This assumes that items_in_room does not have duplicates. If it does, use count(distinct) for the having clause:

having count(distinct item_id) = <n>