SQL SELECT exclude child if parent is present

user_1023216 0
Viewed 52

We have a Projects table where projects can be nested by projectA.parent_id = projectB.id.

When selecting all projects that meet a given criteria, how can we select only the parent if both meet it (or the parent meets it) and only the child if the child meets it?

id parent_id is_chosen
1 null false
2 1 true
3 2 true
4 1 false
5 4 false
6 1 false
7 6 true
8 1 true
9 8 false 
SELECT p.id 
FROM "Projects" p
JOIN "Projects" parent
ON p.parent_id = p.id
WHERE is_chosen = true
  AND ...

The result should be 2,7,8 and not 2,3,7,8. 3 would be excluded because its parent 2 was selected.

What should be included in the AND to accomplish this, or should it be restructured?

postgresql sql
3 Answers

You can union two queries, one for the parents, one for the children. For example:

select distinct *
from (
  select p.* -- finding parents
  from projects p
  join projects c on c.parent_id = p.id
  where p.is_chosen
 union all
  select c.* -- finding children
  from projects p
  join projects c on c.parent_id = p.id
  where not p.is_chosen and c.is_chosen
) x

Result:

 id  parent_id  is_chosen 
 --- ---------- --------- 
 2   1          t         
 8   1          t         
 7   6          t

See example at db<>fiddle.

user_64361910

Should be able to simplify to this:

SELECT p.id
FROM parents p
WHERE is_chosen = 'true'
AND NOT EXISTS (SELECT *
                FROM parents p2
                WHERE p2.is_chosen = 'true'
                AND p2.id = p.parent_id)
user_54200080

You can use CTE like this

WITH CTE AS (
    SELECT *
    FROM tab 
    WHERE is_chosen
)
SELECT
    id
FROM CTE t
WHERE NOT EXISTS (
    SELECT 
    FROM CTE
    WHERE id = t.parent_id
)

Demo

user_58768030
Related