Hi
I am creating a filtering api for an e-shop.
I want the user to select some categories using checkboxes and the api return the appropriate products.
tables:
categories
id - categ_name
1....teens
2....adults
3....t-shirts
4....Shoes
products
id prod_name price
1..t-shirt-a....28
2..t-shirt-b....34
3..shoe-a.....120
product_category_xref
cat_id prod_id
1...........1
3...........1
2...........2
3...........2
4...........3
4...........1
4...........2
filters
filt_id filt_name
1.......age
2......type
filter_cat
filt_id cat_id
1........1
1........2
2........3
2........4
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:
Code:
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
Is there any way to achieve this ?
dBforums
>
Complicated select
01-15-10, 12:43
Linear Mode
