Alternatives to a recursive CTE inside an 'exists' correlated subquery?

Question

I have a situation where I need to be able to see if a given person is within a user/manager hierarchy.

I need to be able to do this for a set of users against a set of rules (don't worry about this, but just to give it a bit of context)

Ideally I'd like to use a recursive CTE inside a correlated exists subquery on the where clause.

But this poses a lot of problems..

1. I don't think you can use a CTE in a subquery at all.
2. I'm on SQL 2005 with compatibility mode 80 - so I can't use cross apply... so no column parameters into UDF's for me :-(

I think what I'm trying to do is:

WITH UserHierarchy(UserId, ManagerId)
    AS
    (
        --Anchor Definition
        SELECT [UserId], [ManagerId] FROM [Users] WHERE [ManagerId] = [Rules].[RuleAddedByUserId] -- this needs to bind to an outer query....
        UNION ALL
        --Recursive Member definiation
        SELECT [Users].[UserId], [Users].[ManagerId] FROM [Users] 
        INNER JOIN [UserHierarchy] ON [Users].[ManagerId] = [UserHierarchy].[UserId]
        WHERE [Users].[UserId] <> [Users].[ManagerId] --don't recurse if the anchor definition matches itself (to avoid an infinate loop).
    )

Is there anyway of making the anchor definition dynamic in compatibility mode 80? Or an alternative approach?

Answer 1

One way to do this would be to create a recursive CTE that has, for each user, one row for each ancestor of that user in the tree. Then you can use the CTE to filter for ancestors. For example, with this tree:

Bob
|-Alice
  |-Jim

The CTE would return something like:

User  Ancestor Level
----  -------- -----
Bob   NULL     1
Alice Bob      1
Jim   Alice    1
Jim   Bob      2

The Level column ends up not being very important, but I found it helped when I was writing the query.

Here's a sample script which identifies all users who are under Alice in the hierarchy:

CREATE TABLE Users(
    UserId int NOT NULL PRIMARY KEY,
    Name nvarchar(25),
    ManagerId int
);
GO

INSERT INTO Users (UserId, Name, ManagerId)
SELECT 1, 'Bob', NULL UNION ALL
SELECT 2, 'Steve', 1 UNION ALL
SELECT 3, 'Chris', 2 UNION ALL
SELECT 4, 'Alice', 1 UNION ALL
SELECT 5, 'Roger', 4 UNION ALL
SELECT 6, 'Tony', 5;
GO

WITH all_ancestors AS (
    SELECT
        u.UserId,
        u.Name,
        u.ManagerId AS AncestorId,
        1 AS level
    FROM
        Users AS u
    UNION ALL
    SELECT
        alla.UserId,
        alla.Name,
        u.ManagerId AS AncestorId,
        alla.level + 1
    FROM
            all_ancestors AS alla
        INNER JOIN
            Users AS u
        ON
            alla.AncestorId = u.UserId
)
SELECT
    u.*
FROM
        Users AS u
    INNER JOIN
        all_ancestors AS a
    ON
        u.UserId = a.UserId
WHERE
    a.AncestorId = 4; -- Alice
GO

DROP TABLE Users;
GO