keep only parent and corresponding child rows linked by parent case ID

user_7701165 0
Viewed 37

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.

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
google-bigquery sql
1 Answers

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
user_812160
Related