SQL query when a table has a link to itself

Question

Hi say I have a table:

Person:
PersonId
Name
ManagerId

So the ManagerId is a reference back to another person.

So there might be a persons in the database:

1
Bob
null

2
Steve
1

3
Tim
2

So Bob is Steve's Manager and Steve is Tim's Manager.

So what I was wanting to do is write a query that gets all the people under Bob's management. Either directly or indirectly. So I would want to get both Steve and Tim. Being in the same line.

If I write:

select * from Person
where ManagerId = 1 I would get only Steve.

How do I write it so I get everyone directly or indirectly under Bob?

Answer 1

You can use Common Table Expressions (CTEs) to solve this problem. CTEs can be used for recursion, as Andrei pointed out (see the excellent reference that Andrei included in his post). Let's say you have a table as follows:

create table Person
(
   PersonId int primary key,
   Name varchar(25),
   ManagerId int foreign Key references Person(PersonId)
)

and let's insert the following data into the table:

insert into Person (PersonId, Name, ManagerId) values 
    (1,'Bob', null),
    (2, 'Steve',1),
    (3, 'Tim', 2)
    (4, 'John', 3),
    (5, 'James', null),
    (6, 'Joe', 5)

then we want a query that will return everyone who directly or indirectly reports to Bob, which would be Steve, Tim and John. We don't want to return James and Bob, since they report to no one, or Joe, since he reports to James. This can be done with a CTE query as follows:

WITH Managers AS 
( 
     --initialize
     SELECT PersonId, Name, ManagerId  
        FROM Person WHERE ManagerId =1
     UNION ALL 
     --recursion 
     SELECT p.PersonId, p.Name, p.ManagerId 
        FROM Person p INNER JOIN Managers m  
        ON p.ManagerId = m.PersonId 
) 
SELECT * FROM Managers

This query returns the correct results:

PersonId    Name                      ManagerId
----------- ------------------------- -----------
2           Steve                     1
3           Tim                       2
4           John                      3

Edit: This answer is valid assuming the OP is using SQL Server 2005 or higher. I do not know if this syntax is valid in MySQL or Oracle.