Recursive CTE in SQL

Question

Union all function is not working inside the Recursive CTE...?

with CTE_Manager(id,manager,man_id,[Level])
as
(
    select id,manager,man_id,1
    from manager
    where man_id is null

union all

    select a.id,a.manager,a.man_id,b.[Level]+1
    from manager a
    join CTE_Manager b
    on b.man_id= a.id
)
select a.manager,ISNULL(a.manager,'SUPER BOSS'),b.Level
from CTE_Manager a
join CTE_Manager b
on a.man_id=b.id

Actually i am getting the output:

I am retrieving the value before the union all function.I have to get all the values from the recursive CTE.

Answer 1

The on clause in your join is the wrong way around. It should be b.id = a.man_id.

What you have done is selected all managers that don't have a manager and then tried to find their manager. When what I suspect you want is all of their subordinates.