SQL Filtering Complex query

Question

I have three tables:

PRODUCT

PRODUCT_ID|NAME
     1    |Elaborado  
     2    |Adidas

PRODUCT_ATTRIBUTE

 PRODUCT_ATTRIBUTE_ID|PRODUCT_ID|ATTRIBUTE_ID|VALUE
         1           |1         |1           |Solid
         2           |1         |2           |Casual
         3           |2         |1           |Solid

ATTRIBUTE

ATTRIBUTE_ID|DESCRIPTION
         1  |Pattern
         2  |Occasion

All the attributes are in ATTRIBUTE table. Link between products and attributes is in PRODUCT_ATTRIBUTE. Now i need to filter products based on attributes:

For ex: I need products whose Pattern is 'Solid' and Occasion is 'Casual':

Result should be as below:

PRODUCT_ID
----------
1

I'm using Mysql database. I have prepared a SQLFiddle: http://sqlfiddle.com/#!9/aa2be/3

Please help.

Thanks, Sash.

Answer 1

Check out this

select group_concat(product.name),attribute.DESCRIPTION,product_attribute.value  from product
join product_attribute
on (product_attribute.product_id=product.product_id)
join attribute
on (product_attribute.attribute_id=attribute.attribute_id)
where (attribute.attribute_id = 1 and product_attribute.value = 'Solid')
or 
(attribute.attribute_id = 2 and product_attribute.value = 'Casual')
group by attribute.attribute_id, product_attribute.value

Answer 2

The concept of storing columns as rows is called Entity-Attribute-Value (EAV) and it makes for complex queries :) For example, you could find "pattern = solid and occasion = casual" like:

select  p.product_id
from    product p
join    product_attribute pa1
on      p.product_id = pa1.product_id
join    attribute a1
on      pa1.attribute_id = a1.attribute_id
join    product_attribute pa2
on      p.product_id = pa2.product_id
join    attribute a2
on      pa2.attribute_id = a2.attribute_id
where   a1.description = 'Pattern'
        and pa1.value = 'Solid'
        and a2.description = 'Occasion'
        and pa2.value = 'Casual'

Example at SQL Fiddle.

An alternate approach is to create a pivoted view, like:

create view vw_product_attribute as
select  p.product_id
,       pa_pattern.value as pattern
,       pa_occasion.value as occasion
from    product p
join    product_attribute pa_pattern
on      p.product_id = pa_pattern.product_id
join    attribute a_pattern
on      pa_pattern.attribute_id = a_pattern.attribute_id
join    product_attribute pa_occasion
on      p.product_id = pa_occasion.product_id
join    attribute a_occasion
on      pa_occasion.attribute_id = a_occasion.attribute_id
where   a_pattern.description = 'Pattern'
        and a_occasion.description = 'Occasion'

You can then query like:

select  product_id
from    vw_product_attribute
where   pattern = 'Solid'
        and occasion = 'Casual'

Example of the alternate approach at SQL Fiddle.

Answer 3

Would this work for you? You'd need to set the description and value on the subquery that set's the patternSolidProduct variable, and the SELECT statement.

You should also put a PK index on attribute.attribute_id, product.product_id and product_attribute.product_attribute_id

I added Indexes to product_attribute.value with a length of 10, attribute.description with a length of 10.

/*Start join method */
/* Get product id's matching pattern = solid */
SET @patternSolidProduct := (SELECT
  GROUP_CONCAT(`pa`.`product_id`)
FROM
  `product_attribute` AS `pa`
LEFT JOIN
  `product` as `p`
ON
  `pa`.`product_id` = `p`.`product_id`
LEFT JOIN
  `attribute` AS `a`
ON
  `a`.`attribute_id` = `pa`.`attribute_id`
WHERE
  `a`.`description` = 'Pattern'
    AND `pa`.`value` = 'Solid'
);
/* End product id's matching pattern = solid */

SELECT
  `pa`.`product_id`
FROM
  `product_attribute` AS `pa`
LEFT JOIN
  `product` as `p`
ON
  `pa`.`product_id` = `p`.`product_id`
LEFT JOIN
  `attribute` AS `a`
ON
  `a`.`attribute_id` = `pa`.`attribute_id`
WHERE
  `a`.`description` = 'Occasion'
  AND `pa`.`value` = 'Casual'
  AND `p`.`product_id` IN (@patternSolidProduct)
/*End join method */

The Explain from the above:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'p', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', 'Using index'
'1', 'SIMPLE', 'pa', 'ref', 'value', 'value', '32', 'const', '1', 'Using where'
'1', 'SIMPLE', 'a', 'eq_ref', 'PRIMARY,description', 'PRIMARY', '8', 'sqlfiddle.pa.attribute_id', '1', 'Using where'