Searching for multiple attributes in MySQL query (with working example)

Question

I have the following tables

table: products

+------------+--------------------+
| product_id | name               |
+------------+--------------------+
| 1          | samsung galaxy s8  |
| 2          | apple iphone 7     |
+------------+--------------------+

table: attributes

+--------------+--------------------+
| attribute_id | name               |
+--------------+--------------------+
| 1            | brand              |
| 2            | color              |
+--------------+--------------------+

table: attribute_values

+--------------------+--------------+------------+---------------------+
| attribute_value_id | attribute_id | product_id | value               |
+--------------------+--------------+------------+---------------------+
| 1                  | 1            | 1          | samsung             |
| 2                  | 2            | 1          | blue                |
| 3                  | 1            | 2          | apple               |
| 4                  | 2            | 2          | red                 |
+--------------------+--------------+------------+---------------------+

And I have the following query's:

Query 1 (WORKS!)

    SELECT
                p.product_id                AS product_id,
                p.name                      AS product_name,
                v.value                     AS attribute_value,
                a.attribute_id              AS attribute_id,
                a.name                      AS attribute_name,
                c.name                      AS attributes_category_name
    FROM
                products p
    LEFT JOIN
                attribute_values v USING (product_id)
    LEFT JOIN
                attributes a USING (attribute_id)
    WHERE
                p.product_id IN (
                    SELECT 
                       p.product_id 
                    FROM 
                       products p 
                    LEFT JOIN 
                      attribute_values v USING (product_id) 
                    LEFT JOIN  
                      attributes a USING (attribute_id) 
                    WHERE  
                      (a.name = 'brand' AND (v.value = 'samsung'))
                )

Query 2 (DOES NOT WORK!)

    SELECT
                p.product_id                AS product_id,
                p.name                      AS product_name,
                v.value                     AS attribute_value,
                a.attribute_id              AS attribute_id,
                a.name                      AS attribute_name,
                c.name                      AS attributes_category_name
    FROM
                products p
    LEFT JOIN
                attribute_values v USING (product_id)
    LEFT JOIN
                attributes a USING (attribute_id)
    WHERE
                p.product_id IN ( 
                    SELECT   
                      p.product_id  
                    FROM   
                      products p  
                    LEFT JOIN   
                      attribute_values v USING (product_id)  
                    LEFT JOIN   
                      attributes a USING (attribute_id)  
                    WHERE   
                      (a.name = 'brand' AND (v.value = 'samsung'))   
                      AND (a.name = 'color' AND (v.value = 'blue'))  
                   )

As you can see, the difference between the 2 queries is in the WHERE-clausule.

Query 1:
--------
p.product_id IN (
    SELECT  
        p.product_id 
    FROM  
        products p 
    LEFT JOIN  
        attribute_values v USING (product_id) 
    LEFT JOIN  
        attributes a USING (attribute_id) 
    WHERE  
        (a.name = 'brand' AND (v.value = 'samsung')) 
   )

Query 2:
--------
p.product_id IN (
    SELECT 
        p.product_id 
    FROM  
        products p 
    LEFT JOIN  
        attribute_values v USING (product_id) 
    LEFT JOIN  
        attributes a USING (attribute_id) 
    WHERE  
        (a.name = 'brand' AND (v.value = 'samsung'))  
        AND (a.name = 'color' AND (v.value = 'blue')) 
   )

I search in query one only for brand > samsung, in query 2 for brand > samsung AND color > blue.

Does anyone know why my second query won't work?

Answer 1

I still do not have reputation enough to comment on stackoverflow, but I think the table c is never declared, so I remove it. This is the way I found to solve your problem using your code.

SELECT
          p.product_id                AS product_id,
          p.name                      AS product_name,
          v.value                     AS attribute_value,
          a.attribute_id              AS attribute_id,
          a.name                      AS attribute_name/*,
          c.name                      AS attributes_category_name*/
FROM
          products p
LEFT JOIN
          attribute_values v USING (product_id)
LEFT JOIN
          attributes a USING (attribute_id)
WHERE 
          p.product_id IN ( 
              SELECT   
                p2.product_id  
              FROM   
                products p2  
              LEFT JOIN   
                attribute_values v USING (product_id)  
              LEFT JOIN   
                attributes a USING (attribute_id)  
              WHERE   
                a.name = 'brand' AND v.value = 'samsung' and p2.product_id IN (
                  SELECT   
                    p3.product_id  
                  FROM   
                    products p3  
                  LEFT JOIN   
                    attribute_values v USING (product_id)  
                  LEFT JOIN   
                    attributes a USING (attribute_id)
                  WHERE a.name = 'color' AND v.value = 'blue'
              )
          )

so basically you should first make one filter after another, not both at the same time, there is no attribute_value that is blue and a samsung. Hope this helps

Answer 2

Look at your WHERE condition in second query:

 WHERE  
  (a.name = 'brand' AND (v.value = 'samsung'))  
  AND (a.name = 'color' AND (v.value = 'blue'))

This condition is ultimately similar to:

WHERE a.name = 'brand' AND v.value = 'samsung'
  AND a.name = 'color' AND v.value = 'blue'

And this will NEVER going to be TRUE for any Record.