CODEWITHSUNDEEP
sqlsql-servercommon-table-expressionrecursive-query
Seda Başkan
Seda Başkan

Reputation: 37

Getting all managers of the user by using CTE on SQL server

I try to get all top managers of the user.

For example. Dan king(Specialist) --> Adam Lion(Product Manager) --> Reina Ken (Head of IT) --> Michael Lorren (Director)--> Pat Deep (CEO)

I want to show top managers of Dan King on one column the below:

Adam
Reina
Michael
Pat

NOTE: Get all employees under manager with CTE I used this link and I decided to take users of manager.

I tried to convert my code according the sample code but it didn't work:

;WITH OS_MANAGERS AS 
(
SELECT USERID, MANAGERUSERID 
from OSMANAGERS
), Manager AS
(
SELECT USERID, MANAGERUSERID
from OS_MANAGERS
where [userID]='mbalasubrama'
union all
SELECT B.USERID, B.MANAGERUSERID
from Manager A
inner join OS_MANAGERS B ON A.MANAGERUSERID= B.USERID
)
select A.USERID , a.MANAGERUSERID
from OS_MANAGERS A
left join Manager B on A.USERID=B.USERID
where A.[userID]='mbalasubrama'

only I see the manager of user, other top ones don't come

RESULT:

Upvotes: 1

Views: 497

Answers (2)

Seda Başkan
Seda Başkan

Reputation: 37

You can see my code: 

WITH ManagerRecursive AS
(
   SELECT  [USERID],[MANAGERUSERID]

  FROM [EBA].[dbo].[OSMANAGERS]

   where [userID]='dking'

    union all

    SELECT tbl.USERID, tbl.MANAGERUSERID

    from ManagerRecursive AS MR

    INNER JOIN [EBA].[dbo].[OSMANAGERS] AS tbl ON tbl.USERID=MR.MANAGERUSERID

)
SELECT * FROM ManagerRecursive ;

Result:

Userid manageruserid

dking   tkir
tkir    skara

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your code seems a bit too complicated... Try this:

DECLARE @tbl TABLE(USERID INT,MANAGERUSERID INT,Name VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,NULL,'CEO Pat')
,(2,1,'Director Michael')
,(3,2,'HIT Reina')
,(4,3,'PM Adam')
,(5,4,'Dan King');

WITH ManagerRecursive AS
(
    SELECT USERID, MANAGERUSERID,Name
    from @tbl
    where [userID]=5
    union all
    SELECT tbl.USERID, tbl.MANAGERUSERID,tbl.Name
    from ManagerRecursive AS MR
    INNER JOIN @tbl AS tbl ON tbl.USERID=MR.MANAGERUSERID
)
SELECT * FROM ManagerRecursive ;

The result

USERID  MANAGERUSERID   Name
5       4               Dan King
4       3               PM Adam
3       2               HIT Reina
2       1               Director Michael
1       NULL            CEO Pat

Upvotes: 2

Related Questions