gstackoverflow gstackoverflow - 2 months ago 6
SQL Question

Write full subordintion chain

I have the following Student table

id boss_id name age
1 NULL NEW_A 10
2 1 NEW_A 12
3 1 NEW_A 14
4 3 NEW_A 16
5 3 NEW_A 18
6 5 NEW_A 20
7 1 NEW_B 22
8 1 NEW_B 24
9 8 NEW_B 26
10 9 NEW_B 28
11 4 NEW_B 44


The following query

select s.id as student, boss.id as boss from Student s, Student boss
where s.boss_id = boss.id --and s.id = 11
order by s.id


retrieves

2 1
3 1
4 3
5 3
6 5
7 1
8 1
9 8
10 9
11 4


I want to return full chain subordination

for example for student 11 it should be

student_id boss_id
11 4
4 3
3 1


Does sql allow to write query according my needs ?

Answer

You can do this with a recursive CTE:

;With Cte As
(
    Select  T.id, T.boss_id
    From    YourTable   T
    Where   T.Id = 11
Union All
    Select  T.id, T.boss_id
    From    YourTable   T
    Join    Cte         C   On  C.boss_id = T.id
                            And T.boss_id Is Not Null
)   
Select  id, boss_id
From    Cte

id  boss_id
11  4
4   3
3   1

SQL Fiddle Live Demo