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_id
s 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?