Scema:
Person(pid:int,
name:char(20),
predecessor:int -> Person.pid
)
Example Table:
pid, name, predecessor
0, 'abc', NULL
1, 'bcd', 0
2, 'cde', 1
3, 'efg', NULL
4, 'fgh', 3
How do i find all successors of a Person 'abc'?
Desired Ouput:
name
'bcd'
'cde'
Many Thanks!
You can do this by generating all the ancestors and then filtering them out. The following is an example for your data:
with recursive cte(pid, lev, ancestor) as (
select pid, 0, predecessor
from person p
union all
select cte.pid, lev + 1, p.predecessor
from person p join
cte
on p.pid = cte.ancestor
)
select p2.name
from cte join
person p1
on cte.ancestor = p1.pid join
person p2
on cte.pid = p2.pid
where p1.name = 'abc';
Here is a SQL Fiddle.
Just generate the rows you need (not all ancestors for all rows) in a recursive CTE:
with recursive cte as (
select p.pid, p.name, 1 AS lvl
from person a
join person p ON p.predecessor = a.pid
where a.name = 'abc'
union all
select p.pid, p.name, c.lvl + 1
from cte c
join person p ON p.predecessor = c.pid
)
select name
from cte
order by lvl;
