SQL query on join table

Question

I've the follow SQL schema:

+----------+
| products |
+----------+
| id       |
| name     |
+----------+
    ^ 8
    |
    v 1
+-------------+
|   values    |
+-------------+
| value       |
| product_id  |
| property_id |
+-------------+
    ^ 8
    |
    v 1
+------------+
| properties |
+------------+
| id         |
| name       |
+------------+

One product has many properties and a property belongs to many products. The values table is the join table for the many_to_many association between products and properties. And in this table is saved the value of the property for a product.

Now I'm looking for a query to select all products with property x with value a, and property y with value b ecc. My try is this query but return no records:

SELECT DISTINCT 
    products.* 
FROM 
    products 
INNER JOIN 
    product_values 
    ON product_values.product_id = products.id 
INNER JOIN 
    properties 
    ON properties.id = product_values.property_id 
WHERE 
    (properties.name = 'size' AND product_values.value = 'big') 
    AND (properties.name = 'color' AND product_values.value = 'red')

If possible I need a query with no nested select.

Answer 1

Since a property can not be color and size at the same time you need to use OR in your where clause. Then group the data and check if both are in the group with having

SELECT products.id, products.name
FROM `products` 
INNER JOIN `product_values` ON `product_values`.`product_id` = `products`.`id` 
INNER JOIN `properties` ON `properties`.`id` = `product_values`.`property_id` 
WHERE (properties.name = 'size' AND product_values.value = 'big') 
   OR (properties.name = 'color' AND product_values.value = 'red')
GROUP BY products.id, products.name
HAVING count(distinct properties.name) = 2