Subordinates of employees

Question

I need to get the subordinates of the employee who is a manager

if we have 5 employees in the table EMPLOYEES

 EMP_NO   MGR_NO     SUB_ORD_PATH
  158           146 
  161           146  
  124           125  
  146           125  
  125           100  
  100           null  
  

i need a query to fill up the data in SUB_ORD_PATH
  like this:

 EMP_NO   MGR_NO     SUB_ORD_PATH
  158          146 
  161          146  
  124          125  
  146          125          ,158,,161,
  125          100          ,124,,146,,158,,161,
  100          null          ,125,,124,,146,,158,,161,

Answer 1

Microsoft has introduced the superb AdventureWorks sample database with SQL Server 2005 (previously pubs & Northwind db-s). 

The HumanResources.Employee table has the self-referencing (FK pointing to PK of the same table) structure you have also.

SQL Server 2005 has also introduced recursive CTE to traverse a tree or part of it.

The following solution applies recursive CTE to get almost the results you need: How to create an organization tree? 

Just reverse the OrgChartPathLabel and use EmployeeID instead of capital letters.  

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

Try:

USE tempdb;
GO
CREATE TABLE dbo.T (
EMP_NO int NOT NULL,
MGR_NO int NULL,
SUB_ORD_PATH varchar(8000) NULL,
CONSTRAINT PK_T PRIMARY KEY (EMP_NO),
CONSTRAINT FK_T_T FOREIGN KEY (MGR_NO) REFERENCES dbo.T(EMP_NO)
);
GO
SET NOCOUNT ON;
INSERT INTO dbo.T(EMP_NO, MGR_NO) VALUES(100, null);
INSERT INTO dbo.T(EMP_NO, MGR_NO) VALUES(125, 100);
INSERT INTO dbo.T(EMP_NO, MGR_NO) VALUES(124, 125);
INSERT INTO dbo.T(EMP_NO, MGR_NO) VALUES(146, 125);
INSERT INTO dbo.T(EMP_NO, MGR_NO) VALUES(158, 146);
INSERT INTO dbo.T(EMP_NO, MGR_NO) VALUES(161, 146);
SET NOCOUNT OFF;
GO
CREATE FUNCTION dbo.GetSubordinates (
@MGR_NO int
)
RETURNS TABLE
AS
RETURN (
WITH SubTree AS (
SELECT EMP_NO, 0 AS lvl
FROM dbo.T
WHERE MGR_NO = @MGR_NO
UNION ALL
SELECT C.EMP_NO, P.lvl + 1
FROM SubTree AS P INNER JOIN dbo.T AS C ON C.MGR_NO = P.EMP_NO
)
SELECT EMP_NO, lvl
FROM SubTree
)
GO
UPDATE
    A
SET
    A.SUB_ORD_PATH = SUBSTRING(B.Subordinates + ',', 2, 8000)
FROM
    dbo.T AS A
    CROSS APPLY
    (
    SELECT ',,' + LTRIM(ST.EMP_NO)
    FROM dbo.GetSubordinates(A.EMP_NO) AS ST
    ORDER BY ST.lvl, ST.EMP_NO
    FOR XML PATH('')
    ) AS B(Subordinates)
WHERE
    EXISTS (
    SELECT *
    FROM dbo.T AS C
    WHERE C.MGR_NO = A.EMP_NO
    );
GO
SELECT * FROM dbo.T;
GO
DROP FUNCTION dbo.GetSubordinates;
GO
DROP TABLE dbo.T;
GO

Inside Microsoft® SQL Server® 2008: T-SQL Querying
http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030/ref=ntt_at_ep_dpi_2

You can learn more about hierarchies from this book. Chapter 12 is about "Graph, Trees, Hierarchies, and Recursive Queries".


AMB