Need help with a SQL CTE Query

Question

I have a table that I need to get some specific data from for a view. Here's the base table structure with some sample data:

| UserID | ReportsToUserID | Org ID |
-------------------------------------
|    1   |      NULL       |    1   |
-------------------------------------
|    2   |       1         |    1   |
-------------------------------------
|    3   |       2         |    1   |
-------------------------------------
|    4   |       3         |    1   |
-------------------------------------

The users will be entering reports and users can see the reports of users who report to them and any users who report to those users. Users who report to no one can see everything in their organization Given my sample data above, user 1 can see the reports of 2, 3, & 4; user 2 can see the reports of 3 & 4; and user 3 can see the reports of 4.

For the view, I'd like to have the data returned as follows:

    | UserID | CanSeeUserID           |  OrgID |
    --------------------------------------------
    |    1   |      2                 |    1   |
    --------------------------------------------
    |    1   |      3                 |    1   |
    --------------------------------------------
    |    1   |      4                 |    1   |
    --------------------------------------------
    |    2   |      3                 |    1   |
    --------------------------------------------
    etc...

Below is my current code, any help is greatly appreciated.

WITH CTEUsers (UserID, CanSeeUserID, OrgID)
AS
(
    SELECT e.ID, e.ReportsToUserID, e.OrgID
    FROM Users e WITH(NOLOCK)
    WHERE COALESCE(ReportsToUserID,0) = 0 --ReportsToUserID can be NULL or 0
    UNION ALL

    SELECT e.ReportsToUserID, e.ID,e.OrgID
    FROM Users e WITH(NOLOCK)
    JOIN CTEUsers c
        ON e.ID = c.UserID
)
SELECT * FROM CTEUsers

Answer 1

Please check whether the below query would satisfy your purpose.

Select A.UserID, B.UserID as 'CanSeeID',A.OrgID from 
USERS A
LEFT OUTER JOIN USERS B
ON ( A.ReportstoUserId  is NULL OR A.ReportstoUserId=0 )
OR A.UserId = B.ReportstoUserId

Answer 2

I know it's not pretty but the only way I found while having a quick look was to run a cursor over the hierarchical query. There are almost certainly better answers from cleverer people than me out there but it's Friday afternoon and I'm off to the pub!

SET NOCOUNT ON;
DECLARE @Users TABLE 
(
    UserID int NOT NULL,
    [Name] varchar(10),
    ReportsToUserID int,
    OrgID int
)

DECLARE @Output TABLE
(
    UserID int,
    CanSeeReportsFrom int
)
INSERT INTO @Users VALUES (1, 'Dan', NULL, 1)
INSERT INTO @Users VALUES (2, 'Tom', 1, 1)
INSERT INTO @Users VALUES (3, 'Dick', 2, 1)
INSERT INTO @Users VALUES (4, 'Harry', 3, 1)

DECLARE @UserID int
DECLARE csr CURSOR FAST_FORWARD FOR
SELECT  COALESCE(UserID, 0)
FROM    @Users

OPEN csr
FETCH NEXT FROM csr INTO @UserID
WHILE (@@FETCH_STATUS = 0)
BEGIN
    ;WITH CTEUsers
    AS
    (
        SELECT  @UserID AS CallingUserID,
                e.UserID, 
                e.ReportsToUserID
        FROM    @Users e
        WHERE   COALESCE(e.ReportsToUserID, 0) = @UserID
        UNION ALL
        SELECT  @UserID,
                e.UserID, 
                e.ReportsToUserID
        FROM @Users e INNER JOIN CTEUsers c ON e.ReportsToUserID = c.UserID
    )
    INSERT INTO @Output 
    SELECT CallingUserID, UserID FROM CTEUsers

    FETCH NEXT FROM csr INTO @UserID
END
CLOSE csr
DEALLOCATE csr
SELECT * FROM @Output

Answer 3

WITH reports (userid, orgid, [Level])
     AS (SELECT e.userid,
                e.orgid,
                0 AS [Level]
         FROM   users e
         WHERE  Coalesce(reportstouserid, 0) = 0
         UNION ALL
         SELECT e.userid,
                e.orgid,
                [Level] + 1
         FROM   users AS e
                INNER JOIN reports AS d
                  ON d.userid = e.reportstouserid)
-- Statement that executes the CTE
SELECT a.[UserID],
       b.userid [CanSeeUserID],
       a.[OrgID]
FROM   reports a
       JOIN reports b
         ON a.[Level] < b.[Level] 

Answer 4

I used the following to create a Temp table with some data that looks like yours:

IF OBJECT_ID('tempdb..#Stage1') IS NOT NULL DROP TABLE #Stage1;
GO

WITH CTEFillTemp AS (
SELECT 1 [UserId], NULL [ReportsToUserID], 1 [OrgId] UNION ALL
SELECT 2 [UserId], 1 [ReportsToUserID], 1 [OrgId] UNION ALL
SELECT 3 [UserId], 2 [ReportsToUserID], 1 [OrgId] UNION ALL
SELECT 4 [UserId], NULL [ReportsToUserID], 2 [OrgId] UNION ALL
SELECT 5 [UserId], 3 [ReportsToUserID], 1 [OrgId] UNION ALL
SELECT 6 [UserId], 4 [ReportsToUserID], 2 [OrgId] UNION ALL
SELECT 7 [UserId], 3 [ReportsToUserID], 1 [OrgId]
) SELECT * INTO #Stage1 FROM CTEFillTemp
GO

SELECT * FROM #Stage1
GO

I then used the following Query:

WITH CTE (UserId, CanSeeUserId, OrgId, Level) AS (
    SELECT #Stage1.ReportsToUserId, #Stage1.UserId, #Stage1.[OrgId], 0 [Level] FROM #Stage1
    UNION ALL
    SELECT #Stage1.ReportsToUserId, CTE.CanSeeUserId, CTE.[OrgId], [Level] + 1 FROM #Stage1
    INNER JOIN CTE ON #Stage1.UserId = CTE.UserId AND #Stage1.[OrgId] = CTE.[OrgId]
)
SELECT *
  FROM CTE
 WHERE [UserId] IS NOT NULL
 ORDER BY UserId, [Level]

This should produce the results you want however as it uses a recursive common table expression it's not a solid solution for unlimited levels. OPTION (MAXRECURSION 99) can help a bit though.