CODEWITHSUNDEEP
mysqlsqljoin
fantua
fantua

Reputation: 77

Select product with properties by group

Product table:

id
1
2
3

Product properties

product_id   prop_id
1            1
1            2
1            3
2            2
2            3
3            1   
4            2
5            1
5            3

Props has groups. Ex, prop_id (1, 2) is a group #1, prop_id (3) - group #2. I need to select product which contains one prop from group #1 and one from group #2.

Like this

select * from product_properties WHERE prop_id IN (1, 2) AND prop_id IN (3)

But it`s doesn't work, how to create similar correct query?

That query must return me products with id 1, 2, 3.

Upvotes: 1

Views: 181

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

SELECT product_id
FROM product_properties
GROUP BY product_id
HAVING COUNT(CASE WHEN prop_id IN (1, 2) THEN 1 END) > 0 -- mean have at least one property with 1 or 2
   AND COUNT(CASE WHEN prop_id = 3       THEN 1 END) = 1 -- mean has property 3

This assume a product cant have same property more than once.

Upvotes: 0

Zoran Horvat
Zoran Horvat

Reputation: 11301

Maybe something like this:

SELECT a.product_id, a.prop_id, b.prop_id
FROM product_properties a, product_properties b
WHERE 
    a.product_id=b.product_id AND 
    a.prop_id IN (1, 2) AND 
    b.prop_id = 3

Upvotes: 1

Related Questions