How to use Common Table Expression and check no duplication in SQL Server

Question

I have a table references to itself.

User table: id, username, managerid and managerid links back to id

Now, I want to get all the managers including direct manager, manager of direct manager, so on and so forth... The problem is that I do not want to have a unstop recursive sql.

So, I want to check if an id alreay in a list, I will not include it anymore.

Here is my sql for that:

with        
    all_managers (id, username, managerid, idlist) as
    (
        select u1.id, u1.username, u1.managerid, ' '
        from users u1, users u2
        where u1.id = u2.managerid
        and u2.id = 6

        UNION ALL

        select u.id, u.username, u.managerid, idlist + ' ' + u.id
        from all_managers a, users u
        where a.managerid = u.id
        and charindex(cast(u.id as nvarchar(5)), idlist) != 0
    )   
select id, username
from all_managers;

The problem is that in this line:

select u1.id, u1.username, u1.managerid, ' '

The SQL Server complains with me that I can not put ' ' as the initialized for idlist. nvarchar(40) does not work as well. I do not know how to declare it inside a common table expression like this one. Usually, in db2, I can just put varchar(40)

My sample data:

ID  UserName  ManagerID

1   admin     1
2   a         1
3   b         1
4   c         2

What I want to do is that I want to find all managers of c guy. The result should be: admin, a, b.

Some of the user can be his manager (like admin) because the ManagerID does not allow NULL and some does not have direct manager.

With common table expression, it can lead to an infinite recursive. So, I am also trying to avoid that situation by trying to not include the id twice. For example, in the 1st iteration, we already have id : 1, so, in the 2nd iteration and later on, 1 should never be allowed.

I also want to ask if my current approach is good or not and any other solutions? Because if I have a big database with a deep hierarchy, I will have to initialize a big varchar to keep it and it consumes memory, right?

Answer 1

Solving the casting issue is easy enough. You simply need to cast both values in the union to the same type:

With
    all_managers (id, username, managerid, idlist) as
    (
        Sselect u1.id, u1.username, u1.managerid
            , Cast(' ' As varchar(40))
        From users As u1
            Cross Join users As u2
        Where u1.id = u2.managerid
            and u2.id = 6
        Union All
        Select u.id, u.username, u.managerid
            , Cast(Coalesce(idlist + ' ','') + Cast(u.id As varchar(10)) As varchar(40))
        From all_managers As a
            Cross Join users As u
        Where a.managerid = u.id
            and CharIndex(Cast(u.id as nvarchar(5)), idlist) != 0
    )   
Select id, username, idlist
From all_managers;

However, I'm still not quite sure what you are trying to achieve. Can you show us some sample data of what should and should not be returned in the resultset?