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.

No, a column only contains 1 value. How could 1 equal 1, 2, and 3 at the same time? — Kleinux, Jan 20 '11 at 19:11
Can you explain how this condition could possibly be true?! `(column = 5 AND column = 8 AND column = 9)` — RichardTheKiwi, Jan 20 '11 at 19:14
@kleinux I assume those are examples and actually would be parameters possibly generated by the application. — jzd, Jan 20 '11 at 19:18

Joe Stefanelli · Accepted Answer · 2011-01-20T20:19:11.617

25

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

edited Jan 20 '11 at 20:19

answered Jan 20 '11 at 19:24

Joe Stefanelli

132,803
19
237
235

1

I think this is what the OP is after, returning movies that have all of the categories and not just one of them.... – Leslie Jan 20 '11 at 20:11
Difficult to scale this i.e. requires dynamic SQL (yuk! :) – onedaywhen Jan 21 '11 at 13:11
This will work, however there are over 3 million searches per day on a fairly large DB. Most are cached, but this will be a heavy query. – Jan 25 '11 at 00:36
1

Works in Postgres, but MySQL gives a syntax error around the count part. Any ideas how to make it work in MySQL? – Ibrahim Muhammad Sep 18 '12 at 01:18

score 8 · Answer 2 · answered Jan 21 '11 at 13:11

8

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

answered Jan 21 '11 at 13:11

onedaywhen

55,269
12
100
138

score 2 · Answer 3 · answered Jan 20 '11 at 19:47

2

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)

answered Jan 20 '11 at 19:47

Kleinux

1,511
10
22

The number of arguments being 3 was just for example purposes. It can range from 1 to over 10. – Jan 20 '11 at 19:51
That's why I wrote this will work if you are using adhoc queries. The expectation is you would build the query dynamically. – Kleinux Jan 21 '11 at 20:17

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

3 Answers3

Linked