0

I have a table in mySQL to store product attributes like this:

------------------------------------
| product_id | att_val1 | att_val2 |
------------------------------------
| 1001001    | x202     | 2200     |
| 1001002    | t100     | 180000   |
| 1001003    | T100     | 1080     |
| 1001001    | T100     | 700      |
| 1001005    | SS120    | 1080     |
| 1001001    | SS120    | 1080     |
| 1001004    | SS120    | 1080     |

I want to get all product_ids that have all the asked properties, I came up with this query

SELECT product_id FROM product_attributes 
WHERE product_id EXISTS (SELECT product_id from product_attributes WHERE att_val1 = 'x202' AND att_val2 = '2200')
AND product_id EXISTS (SELECT product_id from product_attributes WHERE att_val1 = 'SS120' AND att_val2 = '1080')

Is there a better way to achieve this result?

UPDATED (better sample)

I'm going to change the sample data to string values for better clarification

-------------------------------------------
|  product_id  |  attribute  |  value     |
-------------------------------------------
| iPhone 6s    | Screen Size | 4.7        |
| iPhone 7     | Screen Size | 4.7        |
| iPhone 8     | Screen Size | 4.7        |
| iPhone 6s    | Storage     | 64GB       |
| iPhone 7     | Storage     | 128GB      |
| iPhone 8     | Storage     | 64GB       |
| iPhone 6s    | NFC         | FALSE      |
| iPhone 7     | NFC         | FALSE      |
| iPhone 8     | NFC         | TRUE       |

I want to get the iPhone 8 which has Screen Size = 4.7 AND Storage = 64GB AND NFC = TRUE. There could be more than 3 parameters to filter.

SELECT product_id FROM product_attributes
WHERE product_id IN (SELECT product_id from product_attributes 
                        WHERE attribute = 'Screen Size' AND value = '4.7')
AND product_id IN (SELECT product_id from product_attributes 
                        WHERE attribute = Storage AND value = '64GB')
AND product_id IN (SELECT product_id from product_attributes 
                        WHERE attribute = NFC AND value = 'TRUE')
GROUP BY product_id

If I use OR in WHERE statement I will end up having iPhone 6s, 7 and 8.

SELECT product_id FROM product_attributes
WHERE product_id IN (SELECT product_id from product_attributes 
        WHERE (attribute = 'Screen Size' AND value = '4.7') 
        OR (attribute = Storage AND value = '64GB') 
        OR (attribute = NFC AND value = 'TRUE')

So I think I've to repeat the AND product_id IN ... part for as many filters as I have.

I hope I'm wrong! and there is a better way to achieve this result.

0

Your example has syntax errors. Were you hoping to get just 1001001 from that sample data?

One way:

( SELECT product_id from product_attributes
        WHERE att_val1 = 'x202' AND att_val2 = '2200' )
UNION ALL
( SELECT product_id from product_attributes
        WHERE att_val1 = 'SS120' AND att_val2 = '1080' )
GROUP BY product_id
HAVING COUNT(*) = 2;

The best way:

SELECT product_id
    FROM product_attributes AS a
    JOIN product_attributes AS b  USING(product_id)
    WHERE ( a.att_val1 = 'x202'  AND a.att_val2 = '2200' )
      AND ( b.att_val1 = 'SS120' AND b.att_val2 = '1080' )

Both ways will benefit from INDEX(att_val1, att_val2, product_id).

The schema is called EAV (Entity-Attribute-Value) or Key-Value. It is a nuisance for writing queries and for performance. More discussion: http://mysql.rjweb.org/doc.php/eav

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.