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_catego ries.productid = products.produc tid WHERE products_catego ries.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
[CODE=mysql]SELECT *, COUNT(*) as cnt FROM products
INNER JOIN products_catego ries
ON products_catego ries.productid = products.produc tid
WHERE products_catego ries.categoryid IN (1,5,13)
GROUP BY products.produc tid
HAVING cnt >= 3[/CODE]

[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