First we need to join the tables — like this

select p.id as p_id, p.name as parent_name,
       k.* -- we won't need this in later versions
from parent p
join kidatt k on p.id = k.parent_id

now we have two attributes we care about — let make a query that shows those

select p.id as p_id, p.name as parent_name,
       case when k.attribute="intelligence" and k.attribute_value = 5 then 1 else 0 end as has_a1,
       case when k.attribute="health" and k.attribute_value = 4 then 1 else 0 end as has_a2
from parent p
join kidatt k on p.id = k.parent_id

we now have a query with a 1 in a row for those that have each of these

now we group by the parent.

select p.id as p_id, p.name as parent_name,
       SUM(case when k.attribute="intelligence" and k.attribute_value = 5 then 1 else 0 end) as has_a1,
       SUM(case when k.attribute="health" and k.attribute_value = 4 then 1 else 0 end) as has_a2
from parent p
join kidatt k on p.id = k.parent_id
group by p.id, p.name

now we have a query where a1 and a2 are greater than 0 if one or more child has it.

Now just select the results

select * 
from (
  select p.id as p_id, p.name as parent_name,
       SUM(case when k.attribute="intelligence" and k.attribute_value = 5 then 1 else 0 end) as has_a1,
       SUM(case when k.attribute="health" and k.attribute_value = 4 then 1 else 0 end) as has_a2
  from parent p
  join kidatt k on p.id = k.parent_id
  group by p.id, p.name
)
where has_a1 > 0 and has_a2 > 0

note — I did not write this query to be the best way to solve this problem — instead I wrote it in a way to show you how to “think” in SQL and solve the problem with a series of steps.

I’d have to test to be sure, but I expect this would be the fastest way to do this query (depends on data and indexes etc.)

select distinct p.id as p_id, p.name as parent_name,          
from parent p
join kidatt k on p.id = k.parent_id
where k.attribute="intelligence" and k.attribute_value = 5

intersect

select distinct p.id as p_id, p.name as parent_name,          
from parent p
join kidatt k on p.id = k.parent_id
where k.attribute="health" and k.attribute_value = 4