How to use Common Table Expression and check no duplication in SQL Server
- by vodkhang
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?
