How to use Common Table Expression and check no duplication in sqlserver
- 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
--the relation for all the subparts of ozsolar including itself
all_managers (id, username, managerid, idlist) as
(
--seed that is the ozsola part
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 the total number of subparts and group by subpart
select id, username
from all_managers;
The problem is that in this line:
select u1.id, u1.username, u1.managerid, ' '
The sqlserver 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)
