CODEWITHSUNDEEP
sqlsql-servert-sqlrecursioncommon-table-expression
paparazzo
paparazzo

Reputation: 45096

Recursive CTE not getting desired result. How to assign anchor only?

The application is for an employee id get all managers 

declare @emp table (id  int primary key, mgr int);  
insert into @emp values 
       (1, null)
     , (2, 1)
     , (3, 2)
     , (4, null)
     , (5, 4);
select * from @emp;

; with cte as 
( select e.id, e.mgr, cnt = 1 
  from @emp e  
  union all 
  select e.id, e.mgr, cnt + 1
  from @emp e 
  join cte 
    on cte.mgr = e.id 
) 

 select id, mgr, cnt  
 from cte 
 where id = 3;

The above only returns the single row for id = 3. I get that is expected but not what I desire. I want to start (anchor) at 3 and get the manager chain.

If I hard code the anchor I get the desired result.
See below:

; with cte as 
( select e.id, e.mgr, cnt = 1 
  from @emp e 
  where e.id = 3 
  union all 
  select e.id, e.mgr, cnt + 1
  from @emp e 
  join cte 
    on cte.mgr = e.id 
) 

 select id, mgr, cnt  
 from cte;

My question is how to assign the anchor (top part) only in a where on the cte?
If not in the where is there another way to assign the anchor only (not hard coding in the cte)?

Upvotes: 2

Views: 191

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You must decide whether to

  • start with a root node (add WHERE mgr IS NULL or an explicit id to your anchor) and move down the chain, or to
  • start with any child node (add where not exists(SELECT 1 FROM @emp AS x WHERE e.id=x.mgr) to the anchor) and move up or to
  • start with an explicit child (add where e.id=3 to the anchor) and move up.

The general advise is: Start with the narrowest anchor-set possible!

The first query you state in your question (as well as other answers here) will do a huge overdose creating each an any chain starting from everywhere with overlapping results.

As the recursive CTE is a hidden RBAR the engine has no chance to predict its result and will create the full load - just to throw most of this away.

If this is a 1:n relation (always 1 mgr on-top) moving upwards will be much faster. Starting from a given child node You have exactly one step per level - that's it.

Applying the filter in a WHERE after the recursice CTE was worked out, means to create any possible chain just to throw most of them away...

Your second approach is the best I can think of actually.

So the question is: Why do you want to apply this filter at the end?

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

You need to keep the starting position in your cte:

declare @emp table (id  int primary key, mgr int);  
insert into @emp values 
       (1, null)
     , (2, 1)
     , (3, 2)
     , (4, null)
     , (5, 4);

; with cte as 
( select e.id ori, e.id, e.mgr, cnt = 1 
  from @emp e  
  union all 
  select cte.ori,  e.id, e.mgr, cnt + 1
  from @emp e 
  join cte 
    on cte.mgr = e.id 
) 

 select ori, id, mgr, cnt  
 from cte 
 where cte.ori = 3;

Result:

+-----+----+------+-----+
| ori | id | mgr  | cnt |
+-----+----+------+-----+
|   3 |  3 | 2    |   1 |
|   3 |  2 | 1    |   2 |
|   3 |  1 | NULL |   3 |
+-----+----+------+-----+

Upvotes: 3

Michał Turczyn
Michał Turczyn

Reputation: 37377

I made just one change and removed (seemingly) unnecessary column:

declare @emp table (id  int primary key, mgr int);  
insert into @emp values 
       (1, null)
     , (2, 1)
     , (3, 2)
     , (4, null)
     , (5, 4);
select * from @emp;

; with cte as 
( select e.id, e.mgr
  from @emp e  
  union all 
  select cte.id, e.mgr
  from @emp e 
  join cte 
    on cte.mgr = e.id 
) 

 select id, mgr
 from cte 
 where id = 3;

Result is:

id | mgr
3  | 2
3  | 1
3  | NULL

Upvotes: 2

Related Questions