SQL SELECT exclude child if parent is present-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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)

CodePudding user response：

You can use CTE like this

WITH CTE AS (
    SELECT *
    FROM tab 
    WHERE is_chosen = TRUE
)
SELECT
    id
FROM CTE
WHERE parent_id NOT IN (
    SELECT id 
    FROM CTE
)

Demo