MySQL fights query from one to many relationships matching multiple conditions

Question

I have two tables, which are set out approximately as follows:

products            product_attributes
==================  ========================================
| id | name      |  | id | product_id | attribute | value  |
==================  ========================================
| 1  | product 1 |  | 1  | 1          | size      | big    |
| 2  | product 2 |  | 2  | 1          | colour    | red    |
| 3  | product 3 |  | 3  | 2          | size      | medium |
| 3  | product 3 |  | 4  | 2          | age_range | 3-5    |
| .. | ...       |  | 5  | 2          | colour    | blue   |
==================  | 6  | 3          | size      | small  |
                    | .. | ...        | ...       | ...    |
                    ========================================

There are a potentially infinite number of attributes for a product, so they are stored in a separate table.

I want to be able to pull out various products that meet the MULTIPLE conditions (also endlessly), but I can’t think of how to do this without using the OR condition, and then some sort of count to check if all the attributes were matched, I’m sure that this is not the best way, so hopefully someone can help ?!

For example, find products with size = 'medium' and color = 'blue' (this corresponds to product 2 in the example above).

Answer 1

.

, COUNT, , MySQL

SELECT product_id
FROM product_attributes pa
WHERE (attribute='size' and value='medium')
OR (attribute='colour' and value='blue')
GROUP BY product_id
HAVING COUNT(DISTINCT CONCAT(attribute,value) ) = 2

NOT EXISTS , MySQL CTE, .

Answer 2

, :

SELECT a.product_id, p.name FROM product_attributes AS a LEFT JOIN products p ON (a.product_id=p.id) WHERE (a.attribute="size" AND a.value="medium") OR (a.attribute="colour" AND a.value="blue") GROUP BY a.product_id