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

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 a开发者_StackOverflow社区nd 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.

---

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

---

The operator you require is called relational division.

SQL has no direct implementation, you must roll your own. The following article discusses some of the common approaches:

Divided We Stand: The SQL of Relational Division by Joe Celko

---

If you are using adhoc queries you could construct something like this

select *
from item_id t0
left join category_id t1 on t1.itemId=t0.Id and t1.gemres = 5
left join category_id t2 on t2.itemId=t0.Id and t2.gemres = 8
left join category_id t3 on t3.itemId=t0.Id and t3.gemres = 9
where (t1.Id is not null and t2.Id is not null and t3.Id is not null)