How to filter rows that has multiple condition MySQL

Question

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:

1. Brand = Intel
2. 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' 

Answer 1

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