I have a table of cases where child cases are linked to parent cases by a parent_case_id
field that holds the case_id
of the parent case. Cases with null parent_case_id
are parent cases or cases that do not have child cases. I would like to remove cases from this table that do not have child cases and keep only parent cases and their corresponding child cases. I can retain child cases by doing a self join, but having trouble figuring out how to cross-check cases are parent cases.
x
parent_case_id case_id
null abc123
abc123 def456
abc123 ghi789
null jkl123
jkl123 mno456
null pqr789 -- Would want to remove this row for example
Advertisement
Answer
This involves breaking the query up into two parts uses either a CTE or a subquery. Example:
with cases_with_children as (
select
distinct parents.case_id
from cases parents
join cases children
on children.parent_case_id = parents.case_id
)
select cases.*
from cases
left join cases_with_children
on cases.case_id = cases_with_children.case_id
where parent_case_id is not null
or cases_with_children.case_id is not null