CODEWITHSUNDEEP
mysql
Gremash
Gremash

Reputation: 8308

SQL join child with multiple key value pairs

I am trying to write a query for MySQL that selects all parents where there are child records for every condition. My schema is more complicated than this but this is the idea.

For example:

parent_table:

id, ....

child_table

parent_id, id, key, value

So,

SELECT pt.id FROM parent_table pt
JOIN child_table ct ON pt.id = ct.parent_id
WHERE **AllOfTheseExist** (ct.key = "keyName1" AND ct.value = "value1")
                  AND (ct.key = "keyName2" AND ct.value = "value2")

If I 'or' them together it works great but I need it to be exclusive, not inclusive.

Upvotes: 1

Views: 347

Answers (1)

sgeddes
sgeddes

Reputation: 62851

The problem with your query is ct.key cannot equal both keyname1 and keyname2 in the same row. You either need to check the child table multiple times or use conditional aggregation. Here's one option using exists:

SELECT id 
FROM parent_table pt
WHERE EXISTS (
   SELECT 1
   FROM child_table ct
   WHERE pt.id = ct.parent_id
      AND ct.key = 'keyName1' 
      AND ct.value = 'value1') AND EXISTS (
   SELECT 1
   FROM child_table ct
   WHERE pt.id = ct.parent_id
       ct.key = 'keyName2' AND 
       ct.value = 'value2')

Or you could use conditional aggregation:

select id
from parent_table pt 
where exists (
    select 1 
    from child_table ct 
    where pt.id = ct.parent_id
    group by ct.parent_id
    having sum(case when ct.key = 'keyname1' and ct.value = 'value1' 
               then 1 else 0 end) > 0 and 
          sum(case when ct.key = 'keyname2' and ct.value = 'value2' 
               then 1 else 0 end) > 0 )

Maybe one other option using distinct with multiple joins:

select distinct pt.id
from parent_table pt
   join child_table ct1 on pt.id = ct1.parent_id and 
                           ct1.key = 'keyname1' and  
                           ct1.value = 'value1' 
   join child_table ct2 on pt.id = ct2.parent_id and 
                           ct2.key = 'keyname1' and  
                           ct2.value = 'value1'

Upvotes: 1

Related Questions