query to return record from hierarchy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query to return record from hierarchy

Author

Topic

evvo1961
Starting Member

4 Posts

Posted - 2013-10-21 : 02:58:23

I am wanting to run a SQL statement whereby i return the ID of any employee's Director.

The database for employees has a reportsto field which enables me to see the hierarchy of managers above any employee.

There is also a IsDirector flag that indicates a director.

So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.

eg if A reports to B and B reports to C (who is a director) then it returns C.

I basically want the script to run until a director is found.

how would i do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 06:30:44

use a recursive CTE


DECLARE @EmpName varchar(100)
SET @EMpName = <pass a value here>

;With ManagerInfo
AS
(
SELECT ID,Name AS EmpName, ReportsTo, 1 AS level,IsDirector
FROM Table
WHERE Name = @YourEmpName
UNION ALL
SELECT t.ID,t.Name,t.ReportsTo,m.Leval + 1 ,t.IsDirector 
FROM ManagerInfo m
INNER JOIN Table t
ON t.ID = m.ReportsTo
)

SELECT TOP 1 Name
FROM ManagerInfo
WHERE Name <> @EmpName
AND IsDirector = 1
ORDER BY Level
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

evvo1961
Starting Member

4 Posts

Posted - 2013-10-21 : 09:06:37

thanks

i had read up on this but cannot seem to get it working...

this is the code im trying to get working but it returns no records


DECLARE @UserID	INT = NULL
SET @UserID = 595

;With ManagerInfo
AS
(
SELECT u.UserID, u.Username, u.ReportsTo, 1 AS level, p.IsDirector
FROM Users u
INNER JOIN Profiles p
ON u.UserID = p.UserID
WHERE u.UserID = @UserID
UNION ALL
SELECT u.UserID, u.username, u.ReportsTo, m.Level + 1 , m.IsDirector 
FROM ManagerInfo m
INNER JOIN Users u
ON u.UserID = m.ReportsTo
)

SELECT TOP 1 UserID
FROM ManagerInfo
WHERE UserID <> @UserID
AND IsDirector = 1
ORDER BY Level
OPTION (MAXRECURSION 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 09:13:18

its because you're not retrieving isdirector value properly in recursive part
do this small tweak and see


DECLARE @UserID	INT = NULL
SET @UserID = 595

;With ManagerInfo
AS
(
SELECT u.UserID, u.Username, u.ReportsTo, 1 AS level, p.IsDirector
FROM Users u
INNER JOIN Profiles p
ON u.UserID = p.UserID
WHERE u.UserID = @UserID
UNION ALL
SELECT u.UserID, u.username, u.ReportsTo, m.Level + 1 , p.IsDirector 
FROM ManagerInfo m
INNER JOIN Users u
ON u.UserID = m.ReportsTo
INNER JOIN Profiles p
ON u.UserID = p.UserID
)

SELECT TOP 1 UserID
FROM ManagerInfo
WHERE UserID <> @UserID
AND IsDirector = 1
ORDER BY Level
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

evvo1961
Starting Member

4 Posts

Posted - 2013-10-21 : 09:25:04

ah yes thanks that does the trick

many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 09:34:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources