T-SQL query. Choosing a "tree branch" from a table

Question

I have a table with records related to each other

name id manager_id ____________________ adam 1 Null nick 2 1 sten 3 1 iren 4 3 john 5 2 steve 6 3 

I want a stored procedure that will return a bank of managers for the current worker. For example, if I ask that the result of a Steve managers analysis looks as if

 steve 6 3 sten 3 1 adam 1 Null 

How can I do that? (no need to avoid loops now)

Answer 1

Starting with SQL 2005, you can use recursive CTE , as shown below:

 ;WITH Managers AS ( SELECT name, id, manager_id, 1 AS Level FROM YourTable WHERE id = 6 UNION ALL SELECT t.name, t.id, t.manager_id, m.Level + 1 FROM YourTable t JOIN Managers m ON t.id = m.manager_id ) SELECT * FROM Managers ORDER BY Level DESC 

Or, with SQL 2008, you can take a different approach using the new HIERARCHYID data type , which is a new way of representing hierarchical data.

Answer 2

You can use TSQL Common Table Expressions CTE

Use this link below for the link.

http://blog.namwarrizvi.com/?p=25

Answer 3

Something like this should do the trick!

 create table #result ( ident identity name varchar(30), id int, manager_id int ) declare @name varchar(30), @name2 varchar(30) declare @id int, @manager_id int, @manager_id2 int select @name = name, @id = id, @manager_id = manager_id from tablename where name = 'steve' while(@manager_id <> null) begin select @manager_id2 = @manager_id insert into #result(@name, @id, @manager_id) select @name = name, @id = id, @manager_id = manager_id from tablename where id = @manager_id2 end insert into #result(@name, @id, @manager_id) select name, id, manager_id from #result order by ident desc drop table #result 

Answer 4

If your DBMS supports cte, then something like this:

Test Data

 DECLARE @tbl TABLE(name VARCHAR(100),id INT, manager_id INT) INSERT INTO @tbl VALUES ('adam',1,Null), ('nick',2,1), ('sten',3,1), ('iren',4,3), ('john',5,2), ('steve',6,3) 

Inquiry

 DECLARE @id INT=6--Steve ;WITH CTE(name,id,manager_id) AS ( SELECT tbl.name, tbl.id, tbl.manager_id FROM @tbl AS tbl WHERE tbl.id=@id UNION ALL SELECT tbl.name, tbl.id, tbl.manager_id FROM @tbl AS tbl JOIN CTE ON CTE.manager_id=tbl.id ) SELECT * FROM CTE 

Answer 5

 declare @T table ( name varchar(10), id int, manager_id int ) insert into @T values ('adam', 1, Null), ('nick', 2, 1), ('sten', 3, 1), ('iren', 4, 3), ('john', 5, 2), ('steve', 6, 3) declare @id int set @id = 6 ;with C as ( select T.name, T.id, T.manager_id from @T as T where T.id = @id union all select T.name, T.id, T.manager_id from @T as T inner join C on T.id = C.manager_id ) select name, id, manager_id from C 

Answer 6

The solution below uses recursive cte

 USE [temp] GO /****** Object: StoredProcedure [dbo].[temp1] Script Date: 03/12/2012 16:53:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[temp1] @id INT AS WITH DirectReports (name, id, manager_id) AS ( -- Anchor member definition SELECT t.name, t.id, t.manager_id FROM Temp AS t WHERE t.id = @id UNION ALL -- Recursive member definition SELECT t.name, t.id, t.manager_id FROM Temp AS t INNER JOIN DirectReports AS d ON t.id = d.manager_id ) -- Statement that executes the CTE SELECT name, id, manager_id FROM DirectReports GO