SQL Query to display heirarchical data

Question

I have two tables - 'Users' and 'Supervision'

For this example, my users table is very simple:-

Users
=====
ID (PK)
UserName

Some users manage other users, so I've built a second table 'Supervision' to manage this:-

Supervision
===========
UserID
SuperID - this is the ID of the staff member that the user supervises.

This table is used to join the Users table to itself to identify a particular users supervisor. It might be that a user has more than one supervisor, so this table works perfectly to this end.

Here's my sample data in 'Users':-

userID  userName
1       Bob
2       Margaret
3       Amy
4       Emma
5       Carol
6       Albert
7       Robert
8       Richard
9       Harry
10      Arthur

And my data in 'Supervision':-

userID  superID
1       2
1       3
2       4
2       5
3       4
3       5
6       1
6       7
7       8
7       9
9       10

If I want to see who directly reports to Bob, writing an SQL query is straightforward, and tells me that Margaret and Amy are his direct reports.

What I want to do however is to write a query that shows everybody who comes under Bob, so it would need to look at Bobs direct reports, and then their direct reports, and so on - it would give Margaret, Amy, Emma and Carol as the result in this case.

I'm assuming this requires some kind of recursion but I'm completely stuck..

Answer 1

You should use recursive CTE:

WITH RCTE AS 
(
    SELECT * FROM dbo.Supervision WHERE UserID = 1
    UNION ALL
    SELECT s.* FROM dbo.Supervision s 
        INNER JOIN RCTE r ON s.userID = r.superID
)
SELECT DISTINCT u.userID, u.userName 
FROM RCTE r
LEFT JOIN dbo.Users u ON r.superID = u.userID

SQLFiddle DEMO

Answer 2

Sounds to me like you need a Recursive CTE. This article serves as a primer, and includes a fairly similar example to the one you have:

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Hope it helps.