How to avoid Recursive CTE repeating the anchor values

Question

I am using following CTE to get hierarchical structure of manager and employees, i have multiple managers for one employee, in this case i do not want CTE to repeat for that employee again and again as it is doing in my code - getemp() is a simple function returning employeeid, name and managerID

;With hierarchy as
(
select [Level]=1 , * from dbo.getemp() where managerid = 1
union all
select [Level]+1 , e.* from getemp() e
join hierarchy h on h.employeeid = e.managerid

)
Select * from hierarchy

After edit - Following approach working for me. Is it possible with CTE ?

    SET NOCOUNT ON;
    DECLARE @Rows int


    SELECT [Level] = ISNULL(1,0),
       employeeid = ISNULL(employeeid, 0 ),
       empname = CAST(empname as varchar(10)),
       managerid = ISNULL(managerid,0)
       into #Temp1
       from dbo.getemp() as a1
       where a1.managerid = @Top1
       --select * from #Temp1
       SELECT @Rows=@@ROWCOUNT

       DECLARE @I INT = 2;
       while @Rows > 0
       BEGIN

    Insert into #Temp1
    select @I as Level, b.employeeid, b.empname, b.managerid from #Temp1 as e
     inner join (select [employeeid], [empname], [managerid]  from dbo.GetEmp())      as b on b.managerid = e.employeeid
    where e.Level = @I - 1 
    and not exists (
    SELECT 1 FROM #Temp1 t
    WHERE b.employeeid = t.employeeid
    AND b.managerid = t.managerid);
    SELECT @Rows=@@ROWCOUNT
    --SELECT @Rows AS Rows
    IF @Rows > 0
    BEGIN
    SELECT  @I = @I + 1;
    END


END

select distinct * from #Temp1
END

If employee has several managers, which one should be returned in managerID? — James Z, Aug 09 '15 at 06:15
@JamesZ if employee has 2 managers then it should return rows with both managerIDs .. however for next recursion it should consider employee only once and not twice.. — vishakha, Aug 09 '15 at 06:43
Sample data and expected results would be helpful. One of the problems with CTEs is that the recursive query cannot access anything other than the most recently added rows. If you need to access _all_ of the previously processed rows then you may need to use a `WHILE` loop and assemble the results in a temporary table until `@@ROWCOUNT = 0`. — HABO, Aug 09 '15 at 11:31
Thank you Habo. i have tried the approach which you mentioned ..i am editing the question with the code.. still wanted to know the possibility of it being done with CTE.. — vishakha, Aug 09 '15 at 13:16
Could you edit your question to include some sample data that `dbo.GetEmp()` might return? If the only problem is loops in the "hierarchy", e.g. A manages B manages C manages A ..., then it may be possible to handle in a CTE like [this](http://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0). — HABO, Aug 10 '15 at 02:30
FYI: [This](http://stackoverflow.com/questions/30583379/parent-child-sql-recursion/30586065#30586065) answer demonstrates that a CTE is not always the best answer, even if it is possible. A loop may be much more efficient. — HABO, Aug 10 '15 at 12:53

score 0 · Answer 1 · answered Aug 09 '15 at 07:22

0

Since you have several managers, which means that people can also be in several different levels due to having different levels in the manager, you could just take the minimum levels for each branch with something like this:

;With hierarchy as
(
    select [Level]=1 , * from dbo.getemp() where managerid = 1
    union all
    select [Level]+1 , e.* from getemp() e
    join hierarchy h on h.employeeid = e.managerid
)

Select min(Level) as Level, employeeid, name, managerid from hierarchy
group by employeeid, name, managerid

Using a function to return all the employees in every recursion might not be the best solution regarding to performance, especially if it's not an inline function. You might want to consider using for example a temp. table if you can't read the tables themselves directly.

answered Aug 09 '15 at 07:22

James Z

12,209
10
24
44

Thanks for the answer, we have existing function in the system which i need to use... for the sake of asking this question i have given the emp manager scenario.. with your answer still the CTE will recurse for same employees again and again.. which is what i wanted to avoid.. I do not need 'Level' for any specific reason if removing that helps in getting correct query – vishakha Aug 09 '15 at 07:31
@vishakha If you load the data into a temp. table, isn't you still using the function? Why exactly are you trying to avoid same persons being fetched more than once? – James Z Aug 09 '15 at 08:00
@ JamesZ my bad.. i couldn't understand what you were saying.. will try to use temp tables – vishakha Aug 09 '15 at 09:52

score 0 · Accepted Answer · answered Aug 11 '15 at 03:17

Couldnt find the solution using CTE, so I have used the while loop to avoid the repeating anchors, here the code ..

    DECLARE @Rows int
    SELECT [Level] = ISNULL(1,0),
       employeeid = ISNULL(employeeid, 0 ),
       empname = CAST(empname as varchar(10)),
       managerid = ISNULL(managerid,0)
       into #Temp1
       from dbo.getemp() as a1
       where a1.managerid = @Top1
       --select * from #Temp1
       SELECT @Rows=@@ROWCOUNT

       DECLARE @I INT = 2;
       while @Rows > 0
       BEGIN

    Insert into #Temp1
    select @I as Level, b.employeeid, b.empname, b.managerid from #Temp1 as e
     inner join (select [employeeid], [empname], [managerid]  from dbo.GetEmp())      as b on b.managerid = e.employeeid
    where e.Level = @I - 1 
    and not exists (
    SELECT 1 FROM #Temp1 t
    WHERE b.employeeid = t.employeeid
    AND b.managerid = t.managerid);
    SELECT @Rows=@@ROWCOUNT
    --SELECT @Rows AS Rows
    IF @Rows > 0
    BEGIN
    SELECT  @I = @I + 1;
    END
END
select distinct * from #Temp1
END

How to avoid Recursive CTE repeating the anchor values

2 Answers2