CODEWITHSUNDEEP
sql
Jack Queen
Jack Queen

Reputation: 214

the opposite of an IN(), i.e. ANDs not ORs

I am making a query, and want to include all the products that belong to ALL of the filters. I have the filter IDs, e.g. 23,11,82. This would essentially be category IDs. I want to search for all products that belong to category 23,11 and 82. e.g. if 23 meant childrens, 11 meant blue, and 82 meant medium, I would want to search for all the blue childrens' products thats were size medium.

I could only think of using IN(23,11,82), but got stuck, as that would include all the products within each of those categories. So is there a way to change the IN() function to achieve this?

So it would essentially be WHERE categories_id=23 AND categories_id=11 AND categories_id=82

instead of IN which is WHERE categories_id=23 OR categories_id=11 OR categories_id=82

Upvotes: 2

Views: 50

Answers (2)

GavinCattell
GavinCattell

Reputation: 3963

I would use EXISTS in this case. Although it has the limitation of always requiring three filters.

select productid
FROM t
WHERE EXISTS (SELECT ''
FROM f1 
WHERE t.productid = f1.productid
AND f1.category = 23)
AND EXISTS (SELECT ''
FROM f2 
WHERE t.productid = f2.productid
AND f2.category = 11)
EXISTS (SELECT ''
FROM f3 
WHERE t.productid = f3.productid
AND f3.category = 83)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You can do this using group by and having:

select productid
from t
where category in (23, 11, 83)
group by productid
having count(distinct category) = 3;

Upvotes: 4

Related Questions