sql - Using MySQL IN clause as all inclusive (AND instead of OR)

Question

I have a listing of items on a site. Each item has several categories attached to it, let's call these categories movie genres. In the advanced search, I let people check boxes for the genres they like, and it spits out the list of movies matching ANY of the selected genres.

I have a query like this:

AND column IN ('5', '8', '9')

The problem here is that if you select "animation" and "horror" you will get a bunch of Disney cartoons ("animation") and the SAW series ("horror").

I wanted to adapt the search to be all inclusive, so it would only return results matching ALL of the selected genres, so items marked both 'animation' and 'horror' would be returned.

The item_id and category_id pairs are stored in a separate table. So for a movie with an ID 55, there might be 4 genres, so there would be 4 rows with item_id = 55, and category_id equals the 4 category Ids.

Answer 1

EDIT: Modified my answer to more closely match the OP's edited question.

select i.MovieName
    from item i
        inner join ItemCategory ic
            on i.item_id = ic.item_id
    where i.item_id = 55
        and ic.category_id in ('5','8','9')
    group by i.MovieName
    having count(distinct ic.category_id) = 3