CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user3362714
user3362714

Reputation: 159

SQL Recursive CTE - Keep reference to a parent

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

Upvotes: 2

Views: 136

Answers (3)

BeaglesEnd
BeaglesEnd

Reputation: 421

One way would be to use an OR in the join between the UserCTE table and the share table. For example:

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
INNER JOIN [share table] AS s
    ON u.userId = s.userId OR u.managerId s.userId;

However, this has the potential to procedure duplicates if there is greater than one level managerial hierarchy. I.e. A manager also has a manager. An alternative is to make two left joins one on the userId column of the UserCTE table to the share table and the other to managerId column of the UserCTE table to the share table. You can then use a CASE statement on the shareId column to decide which one you require. See below:

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
    u.*
    ,CASE 
        WHEN su.shareId is not null THEN su.shareId
        WHEN sm.shareId is not null THEN sm.shareId
        ELSE null END as shareID
FROM UserCTE AS u
LEFT JOIN [share table] AS su
    ON u.userId = s.userId
LEFT JOIN [share table] AS sm
    ON u.managerId = s.userId;

Hope this helps.

Upvotes: 1

radar
radar

Reputation: 13425

you can do left join on cte table and usershare table.

WITH UserCTE AS (
  SELECT userId, userName, managerId,0 AS steps
  FROM dbo.Users
  where   managerId IS NULL

  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.userId = mgr.managerId
  )
SELECT * FROM UserCTE AS u
left join userShare us
on u.managerId = us.userId
or u.userId = us.userId
order by u.userId

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

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;

Upvotes: 3

Related Questions