CODEWITHSUNDEEP
mysqlsqlaggregate-functionswhere-clausehaving-clause
Hamed
Hamed

Reputation: 25

How to filter rows that has multiple condition MySQL

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'

Upvotes: 0

Views: 1674

Answers (3)

pinguinside
pinguinside

Reputation: 26

i think you have to rethink your database structure, i would suggest you to have field for model and field for brand. so you can do simple 'select' query. And filter the rows.

Upvotes: 0

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Use OR

You should try like below:

SELECT * FROM `wp_product_details_fields` 
WHERE (`field` = 'brand' AND `value` = 'Intel') OR (`field` = 'model' AND `value` = 'Core i7')

Upvotes: 0

Related Questions