How to use multiple IN - Where IN (...) and IN (...)

Question

I am tuning the performance of my script and I have changed the way the message categories are sorted.

I have successfully used the following query. He selects posts with categories (130 | 3 | 4 | 5) and checks to see if she has an init category (73), since a single post can have multiple categories.

SELECT * FROM post LEFT JOIN post_plus ON ( post.id = post_plus.news_id ) WHERE category REGEXP '[[:<:]](130|3|4|5)[[:>:]]' AND category REGEXP '[[:<:]](73)[[:>:]]' AND approve = 1 ORDER BY fixed DESC, date DESC LIMIT 0, 7 

Now with a new query, I need to execute several IN (...) queries instead of AND category REGEXP '...' , but for some reason the additional AND categoryid IN always returns an empty result.

 SELECT * FROM post LEFT JOIN post_plus ON ( post.id = post_plus.news_id ) LEFT JOIN post_category ON ( post_category.postid = post.id ) WHERE categoryid IN ( 130, 3, 4, 5 ) AND categoryid IN ( 73 ) AND approve = 1 ORDER BY fixed DESC, date DESC LIMIT 0, 7; 

Structure

post_category

 +-----+--------+------------+ | cid | postId | categoryId | +-----+--------+------------+ | 824 | 7 | 10 | | 825 | 7 | 13 | | 826 | 7 | 16 | | 827 | 7 | 29 | | 828 | 7 | 71 | +-----+--------+------------+ 

Answer 1

To get the postid for messages that are in category 73 and at least one of category identifiers 130, 3, 4, 5 , you can use

 SELECT postid FROM post_category GROUP BY postid HAVING MAX(CASE WHEN categoryid = 73 THEN 1 END) = 1 AND MAX(CASE WHEN categoryid IN ( 130, 3, 4, 5 ) THEN 1 END) = 1 

This can be used in a view to combine into your broader query.

Or another opportunity

 SELECT c1.postid FROM post_category c1 JOIN post_category c2 ON c1.postid = c2.postid WHERE c1.categoryid IN ( 130, 3, 4, 5 ) AND c2.categoryid = 73 

Answer 2

You can use the INTERSECT -compound statement:

 SELECT postid FROM post_category WHERE categoryid = 73 INTERSECT SELECT postid FROM post_category WHERE categoryid IN (130, 3, 4, 5) 

This will select only those postid for which categoryid is 73 and at least one of categoryid = 130 , 3 , 4 or 5 .

This query is likely to run faster than an aggregate query ( GROUP BY ), since the sql server can use an index on categoryid (which you hope) to get a relevant postid , and then use a temporary index to calculate the intersection (by at least what sqlite does). If there is no postid with categoryid 73, then the second part of the request is never executed, since the intersection of an empty set with something else is always just empty.

To receive your messages, use the subquery:

 SELECT post.* FROM post WHERE post.id IN (SELECT postid FROM post_category WHERE categoryid = 73 INTERSECT SELECT postid FROM post_category WHERE categoryid IN (130, 3, 4, 5)) 

Also note that you can add more INTERSECT components as you like.