MySQL Query filter result

Question

Bellow are my tables with data,

1) product

id    name    
------------
1     abc
2     def
3     xyz

2) product_attribute

id    product_id  attribute_id  attribute_value_id    
--------------------------------------------------
1     1           1             1
2     1           1             2
3     1           2             4
4     2           1             3
5     2           2             5
6     3           1             1
7     1           3             6

3) attribute

id    name
---------------
1     color        
2     size
3     width

4) attribute_value

id    name
--------------
1     Red
2     Blue
3     Black
4     2.5
5     3.5
6     5

When finding the product which has a color = red then get a products 'abc' and 'xyz.

SELECT * 
FROM product AS p 
INNER JOIN product_attribute AS pa ON pa.p_id = p.id 
WHERE (pa.attribute_id = 1 AND pa.attribute_value_id IN(1)) 
GROUP BY p.id

Question

When finding the product which has a color = red and size = 2.5 then return zero results.

SELECT * 
FROM product AS p 
INNER JOIN product_attribute AS pa ON pa.p_id = p.id 
WHERE ((pa.attribute_id = 1 AND pa.attribute_value_id IN(1)) 
  AND (pa.attribute_id = 2 AND pa.attribute_value_id IN(4))) 
GROUP BY p.id

How can I get the product which has color = red and size = 2.5?

Answer 1

If you really want to use a condition like color = 'Red' and size = 2.5 I can suggest using a query with pivoting data like this:

select *
from (
    select p.id, p.name
        , max(case when a.name = 'color' then av.name end) color
        , max(case when a.name = 'size' then cast(av.name as decimal(12,2)) end) size
    from product_attribute pa
    left join product p on pa.product_id = p.id
    left join attribute a on pa.attribute_id = a.id
    left join attribute_value av on pa.attribute_value_id = av.id
    group by p.id, p.name ) t
where
    t.color = 'Red' and t.size = 2.5;

MySQL Fiddle Demo

Answer 2

Finally got the solutions

Using Count()

SELECT p.id,p.name 
FROM product p 
INNER JOIN product_attribute pa ON pa.product_id = p.id 
where (
    (pa.attribute_id = 1 AND pa.attribute_value_id IN(1)) 
    OR (pa.attribute_id = 2 AND pa.attribute_value_id IN(4))
)GROUP BY p.id 
HAVING COUNT(p.id) = 2

You just need to add a count of the filter(like color, size, width etc.).

ex. if you add extra filter width = 5 then you add COUNT(p.id) = 3 in having.

Answer 3

Here are 2 ways using joins to get the products which matches all these attributes and values

Using count()

SELECT p.id,p.name  
FROM product p 
INNER JOIN product_attribute pa ON pa.product_id = p.id 
INNER JOIN attribute a ON a.id = pa.attribute_id
INNER JOIN attribute_value av ON av.id = pa.attribute_value_id
WHERE a.name IN('color','size')
AND av.name IN('red','2.5')
GROUP BY p.id,p.name
HAVING COUNT(DISTINCT a.id) = 2 
AND COUNT(DISTINCT av.id) = 2

By using sum()

SELECT p.id,p.name 
FROM product p 
INNER JOIN product_attribute pa ON pa.product_id = p.id 
INNER JOIN attribute a ON a.id = pa.attribute_id
INNER JOIN attribute_value av ON av.id = pa.attribute_value_id
GROUP BY p.id,p.name 
HAVING SUM(a.name = 'color' AND av.name = 'red') > 0
AND SUM(a.name = 'size' AND av.name = '2.5') > 0

DEMO

Answer 4

If your table is not large you can use exists

    SELECT * FROM product AS p 
            WHERE exists(select * from product_attribute AS pa where pa.p_id = p.id and pa.attribute_id = 1 AND pa.attribute_value_id = 1)  // color = red
                and exists(select * from product_attribute AS pa where pa.p_id = p.id and pa.attribute_id = 2 AND pa.attribute_value_id = 4) // size = 2.5

You can also using join but it is more complicated

SELECT * FROM product AS p 
        INNER JOIN product_attribute AS pa1 ON pa1.p_id = p.id and pa1.attribute_id = 1 AND pa1.attribute_value_id = 1   // color = red
        INNER JOIN product_attribute AS pa2 ON pa2.p_id = p.id and pa2.attribute_id = 2 AND pa2.attribute_value_id = 4  // size = 2.5