SQL: Find a record joined to a single table where same column has different values

Question

I have a table called entities that has many associated records which in turn have names and values (2 more tables).

I need to find a record in entities table that has two associated records with the following conditions:

• one with name "Name 1` and value "Value 1"
• second one with name "Name 2" and value "Value 2"

For now I have such invalid SQL:

SELECT entities.id
FROM   `entities`
       INNER JOIN `records`
               ON `records`.`entity_id` = `entities`.`id`
       INNER JOIN `names`
               ON `names`.`id` = `records`.`name_id`
       INNER JOIN `values`
               ON `values`.`record_id` =`records`.`id`
WHERE  
           `names`.`name` = 'Name 1'
       AND `names`.`resource_type` = 'Type'
       AND ( values.value LIKE '%Value 1%' )
       AND `names`.`name` = 'Name 2'
       AND `names`.`resource_type` = 'Type'
       AND ( values.value LIKE '%Value 2%' )  

This SQL has mutually exclusive conditions and in result it returns nothing. So the question is how to produce a valid SQL to retrieve required records?

Answer 1

Your query returns no rows because you have conflicting conditions in the WHERE clause. If I am following this correctly, you can aggregate and filter with a HAVING clause.

SELECT e.id
FROM entities e
INNER JOIN records r ON r.entity_id = e.id
INNER JOIN names n   ON n.id = r.name_id
INNER JOIN values v  ON v.record_id =r.id
WHERE n.resource_type = 'Type'
GROUP BY e.id
HAVING MAX(n.name = 'Name 1' AND v.value LIKE '%Value 1%') = 1
   AND MAX(n.name = 'Name 2' AND v.value LIKE '%Value 2%') = 1