I have a table that contains my product details
+-----+------------+--------+-------+---------+ | id | product_id | cat_id | field | value | +-----+------------+--------+-------+---------+ | 166 | 121 | 12 | model | Core i7 | | 164 | 121 | 12 | brand | Intel | | 172 | 15 | 12 | model | Core i5 | | 170 | 15 | 12 | brand | Intel | +-----+------------+--------+-------+---------+ 4 rows in set
Now I want to write a query that gives me product_id(s) with this conditions:
- Brand = Intel
- model = Core i7
I’ve tried this one but it didn’t returns any rows, I guess I should use JOIN.
SELECT * FROM `wp_product_details_fields` WHERE `field` = 'brand' AND `value` = 'Intel' AND `field` = 'model' AND `value` = 'Core i7'
Advertisement
Answer
Use group by
and having
:
select product_id from wp_product_details_fields where field in ('model', 'brand') group by product_id having max(field = 'model' and value = 'Core i7') = 1 and max(field = 'brand' and value = 'Intel' ) = 1
Or better yet, using tuple equality:
select product_id from wp_product_details_fields where (field, model) in ( ('model', 'Core i7'), ('brand', 'Intel') ) group by product_id having count(*) = 2