Eav model sql select only products with all 3 values? I have an EAV (Entity Attribute Value) based table for smartphones like the following: 



What if the user wants to see all phones with 
- red color
- steel material

The query is like:

WHERE (attribute_id = color AND value = red)
OR
(attribute_id = material AND value = steel)


In this case the search result would also select product 2 because it has red color but I don't want this, because its material is plastic instead of steel.

You can say group results by product id column having count more than 2 rows per product.

But because it's EAV flexibility, what if the material is a new option for a product, and added to a product after a 1 million values/rows later than its early added values for this product? 

I mean not all specs are one after another right? Isn't that going to slow down the search?

So I need to get products only but only with red and steel (only one option like 2. Product in the table above cant be accepted) and it doesn't matter if those options have millions rows between them it still should find all values for given values of a product.

Is there a solution?
To select more than one attribute:

FROM attribute AS color
JOIN attribute AS material
  ON color.content_id = material.content_id
   AND color.attribute_id = 'color' AND color.value = 'red'
   AND material.attribute_id = 'material' AND material.value = 'steel'


So much flexibility you can strangle yourself on the long sql queries it generates :-)

For other solutions see eav