SQL Recursive CTE - Keep reference to a parent

user3362714 Published at Dev

user3362714

I have a table with the following structure

userId      userName         managerId
----------- ---------------- -----------
1           John             NULL
2           Charles          1
3           Nicolas          NULL
4           Neil             3

And I have another table which has the following

userId      shareId         
----------- -----------
1           1001             
3           1002

So I do the following query to get my recursive CTE:

WITH UserCTE AS (
  SELECT userId, userName, managerId,0 AS steps
  FROM dbo.Users
  WHERE userId = 7

  UNION ALL

  SELECT mgr.userId, mgr.userName, mgr.managerId, usr.steps +1 AS steps
  FROM UserCTE AS usr
    INNER JOIN dbo.Users AS mgr
      ON usr.managerId = mgr.userId
)
SELECT * FROM UserCTE AS u;

Which produces the following result

userId      userName         managerId   steps       
----------- ---------------- ----------- ----------- 
1           John             NULL        0           
2           Charles          1           1           
3           Nicolas          NULL        0           
4           Neil             3           1

OK so what I want to know is the shareId for the users that have it, and also for the users that belong to them.

Expected result :

userId      userName         managerId   steps       shareId
----------- ---------------- ----------- ----------- ----------
1           John             NULL        0           1001
2           Charles          1           1           1001
3           Nicolas          NULL        0           1002
4           Neil             3           1           1002

Is there any way to achieve it?

Thanks

Pரதீப்

Join Share table with the Users table in anchor query of Recursive CTE. Try this.

;WITH UserCTE
     AS (SELECT c.userId,
                userName,
                managerId,
                0 AS steps,
                shareId
         FROM   dbo.Users c
                LEFT JOIN share_table s
                  ON c.userId = s.userId
         WHERE  managerId IS NULL
         UNION ALL
         SELECT mgr.userId,
                mgr.userName,
                mgr.managerId,
                usr.steps + 1 AS steps,
                usr.shareId
         FROM   UserCTE AS usr
                INNER JOIN dbo.Users AS mgr
                        ON usr.userId = mgr.managerId)
SELECT *
FROM   UserCTE AS u
ORDER  BY userId;

Collected from the Internet

Please contact [email protected] to delete if infringement.