Get results from last WHERE condition

Question

I have the following DB design.

PRODUCTS

+----+----------------------------------+
| id | name                             |
+----+----------------------------------+
| 1  | Product 1, Intel, Celeron, N3350 |
| 2  | Product 2, Intel, Celeron, N3350 |
| 3  | Product 3, Intel, i5, 8250U      |
| 4  | Product 4, Intel, i9, 8950HK     |
| 5  | Product 5, Intel, i9, 8950HK     |
+----+----------------------------------+

ATTRIBUTES

+----+--------------+
| id | name         |
+----+--------------+
| 11 | Manufacturer |
| 22 | Type         |
| 33 | Model        |
+----+--------------+

ATTRIBUTE_VALUES

+-----+---------+
| id  | value   |
+-----+---------+
| 111 | Intel   |
| 222 | Celeron |
| 333 | i5      |
| 444 | i9      |
| 555 | N3350   |
| 666 | 8250U   |
| 777 | 8950HK  |
+-----+---------+

And finally, the table that links all of these:

PRODUCT_ATTRIBUTES_VALUES

+------------+--------------+--------------------+
| product_id | attribute_id | attribute_value_id |
+------------+--------------+--------------------+
| 1          | 11           | 111                |
| 1          | 22           | 222                |
| 1          | 33           | 555                |
| 2          | 11           | 111                |
| 2          | 22           | 222                |
| 2          | 33           | 555                |
| 3          | 11           | 111                |
| 3          | 22           | 333                |
| 3          | 33           | 666                |
| 4          | 11           | 111                |
| 4          | 22           | 444                |
| 4          | 33           | 777                |
| 5          | 11           | 111                |
| 5          | 22           | 444                |
| 5          | 33           | 777                |
+------------+--------------+--------------------+

All of these are about filter products.

I want to return the results for each attribute filter.

If I filter by:

• Attribute 'Intel' => it should return all 5 results.
• Type 'Celeron' => it should return 2 results (only Celeron)

SQL-FIDDLE

 SELECT products.id, products.name FROM `products` LEFT JOIN
 `product_attributes_values` ON
 `product_attributes_values`.`product_id`=`products`.`id` LEFT JOIN
 `attributes` ON
 `attributes`.`id`=`product_attributes_values`.`attribute_id` LEFT JOIN
 `attribute_values` ON
 `attribute_values`.`id`=`product_attributes_values`.`attribute_value_id`
 WHERE ((attributes.id = 11 AND `attribute_values`.`id` IN (111)) OR 
 (`attributes`.`id` = 22 AND `attribute_values`.`id` IN (222)) ) GROUP
 BY `products`.`id` LIMIT 10

Answer 1

try this one

SELECT
    products.id,
    products.name
FROM
    `products`
    LEFT JOIN `product_attributes_values`
        ON `product_attributes_values`.`product_id`=`products`.`id`
    LEFT JOIN `attributes`
        ON `attributes`.`id`=`product_attributes_values`.`attribute_id`
    LEFT JOIN `attribute_values`
        ON `attribute_values`.`id`=`product_attributes_values`.`attribute_value_id`
WHERE
    attributes.id = 11
    OR attributes.id = 22
GROUP BY
    products.id,
    products.name
HAVING
    MAX(CASE WHEN attributes.id = 11 THEN attribute_values.id ELSE NULL END) IN (111)
    AND MAX(CASE WHEN attributes.id = 22 THEN attribute_values.id ELSE NULL END) IN (222)
LIMIT
    10

if you know all your ids, then you don’t need all these joins

SELECT
    p.id,
    p.name
FROM
    `products` as p
    LEFT JOIN `product_attributes_values` as pav1
        ON  p.id = pav1.product_id
            and pav1.attribute_id = 11
    LEFT JOIN `product_attributes_values` as pav2
        ON  p.id = pav2.product_id
            and pav2.attribute_id = 22
WHERE
    pav1.attribute_value_id = 111
    AND pav2.attribute_value_id = 222

Answer 2

It is kind of a relational division problem, here is one solution:

SELECT products.id, products.name
FROM products
WHERE EXISTS (
  SELECT 1
  FROM product_attributes_values
  INNER JOIN attributes ON product_attributes_values.attribute_id = attributes.id
  INNER JOIN attribute_values ON product_attributes_values.attribute_value_id = attribute_values.id 
  WHERE product_attributes_values.product_id = products.id AND (
    (attributes.attribute_name = 'Manufacturer' AND attribute_values.value = 'Intel®') OR
    (attributes.attribute_name = 'Type'         AND attribute_values.value = 'Celeron®')
  )
  HAVING COUNT(*) = 2 -- this must match the number of conditions
)