What i want to do is when the user selects 2 categories from the same filter the query to return the products that belongs either the 1 either the other.
For example teens, adults should return 1..t-shirt-a,2..t-shirt-b
If the user selects categories from different filters the query should work substractive which means that the product should belong to both the categories of the different filters to be returned. For example teens, t-shirts
should return 1..t-shirt-a.
currently iam using a query like:
SELECT prd.id FROM product AS prd INNER JOIN product_category_xref AS cat
ON cat.prod_id=prd.id WHERE cat.cat_id IN (1,3)
GROUP BY cat.prod_id HAVING COUNT(cat.cat_id)=2
The above query does not function properly when 2 categories from the same filter are being selected