CODEWITHSUNDEEP
sql-serversql-server-2005
Naveen
Naveen

Reputation: 124

Return all non leaf nodes using Recursive CTE in MS SQL

I have a table with data

id       name       mgtId
--------------------------
1        joe         null
2        jack         1
3        jill         1
4        paul         2
5        ron          4
6        sam          2

mgtId references id. How can I select non leaf nodes(joe, jack, paul) using CTE.

Upvotes: 0

Views: 962

Answers (3)

dani herrera
dani herrera

Reputation: 51695

One way:

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

UPDATED 25 aprl 2012

I come to test above query and it works and also return root node:

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

results:

id name    mgtId id 
-- ------- ----- -- 
1  joe     null  1  
2  jack    1     2  
4  paul    2     4

Upvotes: 1

Hemanth
Hemanth

Reputation: 1

with cte as( select id,mgtId from mgr_emp ) select b.id,b.name ,b.mgtId ,a.mgtId from cte a right join mgr_emp b on b.mgtId =a.id

Upvotes: 0

therealmitchconnors
therealmitchconnors

Reputation: 2760

select *
from table
where id in (select mgtId from table)

Upvotes: 1

Related Questions