Return all non leaf nodes using CTE in MS SQL

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

select * 
into #your_table
from (
select 1 as id, 'joe' as name, null as mgtId union all
select 2,        'jack   ',      1  union all
select 3,        'jill  ' ,      1 union all
select 4,        'paul '  ,      2 union all
select 5,        'ron '   ,      4 union all
select 6,        'sam'    ,      2  ) T


;with parents_id as (
   select distinct mgtId as id
   from #your_table
)
select *
from #your_table t
inner join parents_id p on t.id = p.id

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results