Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Unanswered: Complicated select

    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 ?
    Last edited by sakis; 01-15-10 at 13:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •