jiaweizhang jiaweizhang - 5 months ago 10
SQL Question

Common Table Expression Where Roots are not present in both columns

This is a variation of the normal CTE where you find all parents of a node. Only difference is:


  • The greatest descendant does not have a
    null
    child. Instead, it's simply not present in the
    child
    column.



Here is my attempt:

insert into t (parent, child) values (1, 2)
insert into t (parent, child) values (1, 3)
insert into t (parent, child) values (4, 2)
insert into t (parent, child) values (2, 5)
insert into t (parent, child) values (2, 6)
insert into t (parent, child) values (9, 6)
insert into t (parent, child) values (6, 7)
insert into t (parent, child) values (6, 8)

with cte as
(
select child, parent, 0 as level
from t
where parent = 5
union all
select q.child, q.parent, level+1
from t q
inner join cte as c on c.parent= q.child
)

select distinct parent from cte
where parent <> 5


In this case, when I try to get all parents for
5
, nothing is found because
5
isn't a parent to anyone. If I try to find all parents for
2
, it succeeds however because
2
is a parent to
5
and
6
.

Answer

If 5 is not a parent to anyone, then your filter parent=5 will never give any output. 5 is a child and you want to find all parent above it:

declare @t table (parent int, child int)

insert into @t (parent, child)
values (1, 2),
 (1, 3),
 (4, 2),
 (2, 5),
 (2, 6),
 (9, 6),
 (6, 7),
 (6, 8)

;with cte as
( 
    select child, parent, 0 as level 
    from @t
    where child = 5 ---<<<
    union all
    select q.child, q.parent, level+1 
    from @t q
    inner join cte as c on c.parent= q.child
)

select distinct parent from cte 
where parent <> 5

enter image description here

Comments