SQL: WITH common_table_expression for finding direct/indirect subordinate of manager

http://msdn.microsoft.com/en-us/library/ms175972.aspx

WITH common_table_expression

Example: 1 : WITH common_table_expression for finding direct/indirect subordinate of manager

USE AdventureWorks2008R2;

GO

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel

FROM dbo.MyEmployees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1

FROM dbo.MyEmployees AS e

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

ORDER BY ManagerID;

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s