SQL Server recursive common table expression how to get only matching manager record

Question

I am new to CTE in SQL Server. I am trying with the example from https://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/

I wan to get only the employees reporting to a manager who are having a record in another table i.e UserTag table eg: In the following table, UserId: 100 is what my test user, he has got following manager hierarchy. Someone in his manager hierarchy, got a record in UserTag. i want that manager id.

This should happen for all users in 'User' table.

User
====
UserId  ManagerId
100     101
101     102
102     103
103     104

UserTag
========
Id  UserId  TagId   TagName
1   103     1       'test'

Expected Result:
UserId  ManagerWithTagId
100     103

WITH Managers AS 
( 
    -- initialization 
    SELECT Id, Email, ManagerID
    FROM dbo.[User] 

    UNION ALL 

    -- recursive execution 
    SELECT u.Id, u.Email, u.ManagerID
    FROM dbo.[User] u 
    INNER JOIN Managers m ON m.ManagerID = u.ID
    --INNER JOIN UserTag UT ON UT.UserId = u.ID
    WHERE UT.TagName = 'test'
) 
SELECT * 
FROM Managers

With the above query i.e commented the UserTag table. i am getting the complete manager hierarchy of particular user. But not sure how to proceed to get my expected result?

Can anyone help me how to achieve this?

Please show the table structure (by providing the SQL DDL), and provide some sample data and expected results from your query — marc_s, May 03 '22 at 14:07
Are you *sure* you need a rCTE? *"I wan to get only the employees reporting to a manager who are having a record in another table"* Suggests you want an `EXISTS`. Please, however, see [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/2029983); what specifically is it you need help with? **Consumable** sampled and expected results will help us help you. — Thom A, May 03 '22 at 14:08

score 0 · Answer 1 · answered May 04 '22 at 08:05

You can try something like this, I may have hacked the later bit as did find that a bit hard to get. I can get the hierarchy, imagine there are better people who know a nice way for the last bit.

CREATE TABLE dbo.Usr (
    UserId INT NOT NULL CONSTRAINT PK_Usr PRIMARY KEY,
    ManagerId INT NULL
);

INSERT INTO dbo.Usr (UserId, ManagerId)
VALUES 
(100, 101),
(101, 102), 
(102, 103), 
(103, 104),
(104, NULL);

CREATE TABLE dbo.UsrTag (
    Id INT IDENTITY(1,1) CONSTRAINT PK_UsrTag PRIMARY KEY, 
    UserId INT NOT NULL, 
    TagId INT NOT NULL, 
    TagName NVARCHAR(10)
);

INSERT INTO dbo.UsrTag (UserId, TagId, TagName)
VALUES
(103, 1, 'test'),
(102, 3, 'test2');


---------------------------------------
GO

DECLARE @UserId INT = 100;

WITH Managers AS (
    SELECT UserId, ManagerId, 1 AS [Level]
    FROM dbo.Usr
    WHERE UserId = @UserId

    UNION ALL

    SELECT U.UserId, U.ManagerId, M.[Level] + 1
    FROM Managers AS M
    INNER JOIN dbo.Usr AS U ON U.UserId = M.ManagerId
)

-- SELECT * FROM Managers

-- Try and now use the data and filter out any user tags not matching the required value
SELECT TOP 1 
M.UserId, 
UT.UserId AS TopLevelManagerUserId
FROM Managers AS M
LEFT JOIN dbo.UsrTag AS UT ON UT.TagName = 'test'
CROSS APPLY (
    SELECT TOP 1 M1.UserId 
    FROM Managers AS M1
    LEFT JOIN dbo.UsrTag AS UT1 ON UT1.TagName = 'test'
    ORDER BY [Level] DESC
) AS T
ORDER BY M.[Level]

It was based on this one Recursively get last record from same table parent child

SQL Server recursive common table expression how to get only matching manager record

1 Answers1