Help with SELECT id IN query

[imaginit]

I need to build a query that queries products based on multiple categories.

A product can have multiple categories attached to it and I want to query products that are only in ALL of the selected categories.

My query might look like:

SELECT * FROM products INNER JOIN products_categories.productid = products.productid WHERE products_categories.categoryid IN (1,5,13)

The results I want is for the products that are returned would be in ALL three categories, 1, 5, and 13, however, that query would return products that were in ANY of those categories.

How can I alter this query to have the desired results? Thank you in advance!

[SLauren]

Find the sum of all the selected values as you mentioned (1,5,13) and use the sum in the below query along with all the selected values:
// Assume total = 1 + 5 + 13 = 19
// First_Val = 1, Second_Val = 5 and Third_Val = 13

SELECT * FROM products WHERE products.productid IN (SELECT products_categories.productid FROM products_categories GROUP BY products_categories.productid HAVING SUM(IF(products_categories.categoryid = First_Val ,First_Val ,IF(products_categories.categoryid = Second_Val ,Second_Val ,IF(products_categories.categoryid = Third_Val ,Third_Val ,total + 1)))) = total);

I have placed the IF conditions inside the query as the sum of some other set of selected values may give the same total,e.g., 1 + 8 + 10 = 19....etc.

I am not sure at this point whether you want to include a product which contains a set of values like : 1,5,13,15,20....etc. Because this product also contains all the values which you have selected.If this is the case then you can change "total + 1" to 0 and then this query will result those products also.

Hope this could help.

Thanks,
Lauren

[mwasif]

I hope this will fulfill your requirement

SELECT *, COUNT(*) as cnt FROM products 
INNER JOIN products_categories 
ON products_categories.productid = products.productid
WHERE products_categories.categoryid IN (1,5,13)
GROUP BY products.productid
HAVING cnt >= 3

[SLauren]

Thanks mwasif for providing this nice snippet :-).

Cheers,
Lauren....

[imaginit]

Thanks everyone! I am going to give this a try and see how it works out.

Lance