CODEWITHSUNDEEP
mysql
Marian
Marian

Reputation: 1

MYSQL: product - attribute relations

I'm trying to figure out relations between products and product attributes. I have a product table and a product_attributes table. Product can have multiple attributes. I need a query for finding all products which have ONE specific attribute AND one of another ones. E.g. product with attribute_value_id "1" AND this product must also have attribute_value_id IN (2, 3, 4)

I thought this would do it, but it does not:

SELECT DISTINCT p.product_id 
FROM `product` p 
JOIN `product_attribute` pa ON (p.product_id = pa.product_id) 
WHERE pa.attribute_value_id = 1 AND pa.attribute_value_id IN(2, 3, 4)

product

product_id

product_attribute

product_id | attribute_id | attribute_value_id

attribute_id is attribute group (e.g. color, gender) attribute_value_id is specific value (e.g. blue, men, women)

In another words I need all prodcuts for men which are blue or red.

Any suggestions? Thank you in advance. Any help appreciated.

Upvotes: 0

Views: 393

Answers (1)

Shuwn Yuan Tee
Shuwn Yuan Tee

Reputation: 5748

Probably a bit late here, but I guess late is better than never :p

The subquery select records where either condition 1 or condition 2 matches. If a product matches both conditions, 2 rows will appear in the result.

The outer query do a count() on the result & check for count = 2, which output distinct product where 2 conditions are matched.

-- outer query
SELECT
    a.product_id, count(a.attribute_value_id) as attribute_count
FROM

-- subquery
(
    SELECT p.*, pa.attribute_value_id
    FROM
        product p
        JOIN product_attribute pa 
            ON (p.product_id = pa.product_id) 
    WHERE
        -- condition 1
        pa.attribute_value_id = 1 
        -- condition 2
        OR pa.attribute_value_id IN(2, 3, 4)
) a

GROUP BY a.product_id
HAVING attribute_count = 2;

Upvotes: 0

Related Questions