Let's say I have:

• a parents table with the following columns: (id, name)
• a children attributes table with the following columns: (id, child_id, parent_id, attribute, attribute_value)

Now I want to filter any parent id's that has at least a child with both:

• attribute => intelligence of 5
• attribute => health of 4

Either one child with intelligence of 5 and health of 4, or one child has intelligence of 5 and another child has health of 4.

How would you query that, in PostgreSQL? Thank you

CodePudding user response：

You can just do the intersection of

• parents that have children with intelligence 5
• parents that have children with health 4

(SELECT parent_id 
 FROM tab
 WHERE attribute = 'intelligence'
   AND attribute_value = 5       )
INTERSECT
(SELECT parent_id 
 FROM tab
 WHERE attribute = 'health'
   AND attribute_value = 4       )

CodePudding user response：

If you only wants parents info:

SELECT 
  DISTINCT parents.id, parents.name 
FROM 
  parents 
  LEFT JOIN attributes ON parents.id = attributes.parent_id 
WHERE 
  (attribute = 'intelligence' AND attribute_value = 5)
   OR (attribute = 'health' AND attribute_value = 4)

CodePudding user response：

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