1FJVbVv6

1Alternatives to a recursive CTE inside an 'exists' correlated subquery?
2WITH UserHierarchy(UserId, ManagerId)
3    AS
4    (
5        --Anchor Definition
6        SELECT [UserId], [ManagerId] FROM [Users] WHERE [ManagerId] = [Rules].[RuleAddedByUserId] -- this needs to bind to an outer query....
7        UNION ALL
8        --Recursive Member definiation
9        SELECT [Users].[UserId], [Users].[ManagerId] FROM [Users] 
10        INNER JOIN [UserHierarchy] ON [Users].[ManagerId] = [UserHierarchy].[UserId]
11        WHERE [Users].[UserId] <> [Users].[ManagerId] --don't recurse if the anchor definition matches itself (to avoid an infinate loop).
12    )
13	
14Bob
15|-Alice
16  |-Jim
17	
18User  Ancestor Level
19----  -------- -----
20Bob   NULL     1
21Alice Bob      1
22Jim   Alice    1
23Jim   Bob      2
24	
25CREATE TABLE Users(
26    UserId int NOT NULL PRIMARY KEY,
27    Name nvarchar(25),
28    ManagerId int
29);
30GO
31
32INSERT INTO Users (UserId, Name, ManagerId)
33SELECT 1, 'Bob', NULL UNION ALL
34SELECT 2, 'Steve', 1 UNION ALL
35SELECT 3, 'Chris', 2 UNION ALL
36SELECT 4, 'Alice', 1 UNION ALL
37SELECT 5, 'Roger', 4 UNION ALL
38SELECT 6, 'Tony', 5;
39GO
40
41WITH all_ancestors AS (
42    SELECT
43        u.UserId,
44        u.Name,
45        u.ManagerId AS AncestorId,
46        1 AS level
47    FROM
48        Users AS u
49    UNION ALL
50    SELECT
51        alla.UserId,
52        alla.Name,
53        u.ManagerId AS AncestorId,
54        alla.level + 1
55    FROM
56            all_ancestors AS alla
57        INNER JOIN
58            Users AS u
59        ON
60            alla.AncestorId = u.UserId
61)
62SELECT
63    u.*
64FROM
65        Users AS u
66    INNER JOIN
67        all_ancestors AS a
68    ON
69        u.UserId = a.UserId
70WHERE
71    a.AncestorId = 4; -- Alice
72GO
73
74DROP TABLE Users;
75GO
76	
77… /* your main query here */
78WHERE …
79  AND EXISTS (
80    SELECT *
81    FROM [Users] u1
82    WHERE [UserID] = @UserID
83      AND (
84        [ManagerId] = [Rules].[RuleAddedByUserId]
85        OR EXISTS (
86          SELECT *
87          FROM [Users] u2
88          WHERE [UserID] = u1.[ManagerID]
89            AND (
90              [ManagerId] = [Rules].[RuleAddedByUserId]
91              OR EXISTS (
92                SELECT *
93                FROM [Users] u3
94                WHERE [UserID] = u2.[ManagerID]
95                  AND (
96                    [ManagerId] = [Rules].[RuleAddedByUserId]
97                    OR EXISTS ( … /* and so on, until you've covered
98                                     all possible levels */
99                    )
100                  )
101              )
102            )
103        )
104      )
105  )
106	
107RulesUserHierarchy(UserId, ManagerId, PushRuleId, OnlyForSubOrdinates) -- Gets only subordinates for rules created by managers. And all users for those created by admin.
108AS
109(
110    --Anchor Definition
111    SELECT
112         [Users].[UserId]
113        ,[Users].[ManagerId]
114        ,[RulesAnchor].[PushRuleId]
115        ,[RulesAnchor].[OnlyForSubOrdinates]
116    FROM [Users]
117        CROSS JOIN [Rules] [RulesAnchor] --assume every user is doing every rule at this point (because the recursive statement has to be the first statement), we'll filter later.
118    WHERE (([OnlyForSubOrdinates]) = 0 OR ([OnlyForSubOrdinates] = 1 AND [UserId] = [RulesAnchor].[AddedByUserId]))
119    UNION ALL
120
121    --Recursive Member definiation
122    SELECT
123         [Users].[UserId]
124        ,[Users].[ManagerId]
125        ,[RulesUserHierarchy].[PushRuleId]
126        ,[RulesUserHierarchy].[OnlyForSubOrdinates]
127    FROM [Users]
128        INNER JOIN [RulesUserHierarchy]
129            ON [Users].[ManagerId] = [RulesUserHierarchy].[UserId] --recursive hook 
130            AND [RulesUserHierarchy].[OnlyForSubOrdinates] = 1 -- no point recursing if it's for everyone, as the anchor will pull back everything for us.
131    WHERE [Users].[UserId] <> [Users].[ManagerId] --don't recurse if the anchor definition matches itself (to avoid an infinate loop).
132)       
133-- simple statement to test recursion above, will be filtering the inclusions here (e.g. the other mega exists statements)
134SELECT [UserId], [ManagerId], [PushRuleId], [OnlyForSubOrdinates] FROM [RulesUserHierarchy]