SQL query when a table has a reference to itself

Question

Hi, I have a table:

Person: PersonId Name ManagerId 

So, ManagerId refers to another person.

Thus, there can be people in the database:

 1 Bob null 2 Steve 1 3 Tim 2 

So Bob is the Steve Manager, Steve is the Tim manager.

So, what I wanted to do was write a query that would get all the people running Bob. Directly or indirectly. Therefore, I would like to get both Steve and Tim. Be on the same line.

If I write:

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

How can I write it so that I get everything directly or indirectly under Bob?

Answer 1

You can use Common Table Expressions (CTE) to solve this problem. CTEs can be used for recursion, as Andrew noted (see. The excellent link that Andrey 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 paste 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 need a request that will return to anyone who directly or indirectly tells Bob that Steve, Tim and John will be. We do not want to return James and Bob because they are not telling anyone, or Joe because he is telling James. This can be done using a CTE request 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 if the OP uses SQL Server 2005 or higher. I do not know if this syntax is valid in MySQL or Oracle.

Answer 2

If you are using MS SQL Server 2005 or later, you can use CTE, as @AndreiDrynov noted, for example:

 ;WITH Emps(PersonId, Name, PersonLevel, ManagerName) AS ( SELECT PersonId, Name, 0 AS PersonLevel, CONVERT(NVARCHAR(50), 'No Manager') AS ManagerName FROM Persons WHERE ManagerId IS NULL Union All SELECT p.PersonId, P.Name, e.PersonLevel + 1 , e.Name FROM Persons p INNER JOIN Emps e ON p.ManagerId = e.PersonId ) SELECT * FROM Emps WHERE PersonLevel <= 2 

This query should give you the following:

  PersonId | Name | Peroson Level | Manager ------------+---------+------------------+-------------- 1 Bob 0 No Manager 2 Steve 1 Bob 3 Tim 2 Steve 

You can see it in action here:

Demo

Answer 3

 select * from Person A inner join Person B ON B.PersonID = A.ManagerID where ManagerId = 1