MySQL query WHERE and AND on multiple rows

Question

Hi i have question about a MySQL query.

I have the following table:

              product_properties             <-- table name
+------------------------------------------+    
|  id  |  property  |  value  | productid  | <-- field names
+------------------------------------------+
|   1  |  example   |    230  |         3  | +-- rows
|   2  |  power     |    110  |         3  | |
|   3  |  voltage   |    1.2  |         4  | |
|   4  |  example   |    240  |         4  | |
|   5  |  example   |    320  |         6  | |
|   6  |  power     |    100  |         4  | |
|   7  |  power     |    110  |         6  | +
+------------------------------------------+

I want to create a filter query. For example:

want to see all the products with 'example' that match with '230'. This will return one item, is i use:

SELECT * FROM product_properties WHERE property='example' AND value='230';

that was easy, but now i want to show all the products with 'example' that match with '230' and 'power' that match with '110'.

i tried the following:

SELECT * FROM product_properties WHERE property='example' AND value='230' AND property='power' AND value='110';

SELECT * FROM product_properties WHERE property='example' AND value='230' OR property='power' AND value='110';

problem with the first query is, it will return empty, i get that. problem with the second query is, that it is returning also the products with property 'example 230,240,320'.

my question is what is the best way to use on this or shouldn't i create a dynamic product properties table?

Answer 1

You have to filter on either or (since both cases can't match for 1 record) and then find the properties where both match with a having clause

SELECT product_id 
FROM product_properties 
WHERE property='example' AND value='230' 
OR property='power' AND value='110'
group by product_id
having count(distinct property) = 2