Efficient SQL query to return parents that satisfy constraints on children I've got a schema something like having an Entity table, with a to-many relationship to Attribute, which has a to-many relationship to Value. Attribute has a column Name, and Value has a column Value:

Entity
------
Id
Name


Attribute
---------
Id
EntityId
Name


Value
-----
Id
AttributeId
Value


I need to be able to construct a query that will return only Entity rows that have certain combinations of attribute/value children. Currently I'm writing the query something like:

select
    *
    from Entity e   
    where exists(
        select * from Attribute a 
            join Value v on v.AttributeId = a.id 
            where a.EntityId = e.id and a.Name = 'color' and v.Value = 'red')
    and exists(
        select * from Attribute a 
            join Value v on v.AttributeId = a.id 
            where a.EntityId = e.id and a.Name = 'size' and v.Value = 'small')


This works, but every extra attribute filter adds another exists clause, and I'm nervous about performance. Is there a more efficient way to write queries of this type, or is it likely to be perfectly performant if I use correct indexes etc?
You need something like that:

SELECT e.Id, count(1) AS cnt
  FROM Entity    AS e
  JOIN Attribute AS a ON a.EntityID = e.Id
  JOIN Value     AS v ON v.AttributeId = a.Id
 WHERE (a.Name = 'color' AND v.Value = 'red')
    OR (a.Name = 'size'  AND v.Value = 'small')
--  OR ( ... ) -- extended here
 GROUP BY e.Id
HAVING cnt = 2 -- exact number of desired attributes
;