Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 recursive query

Author  Topic 

wided
Posting Yak Master

218 Posts

Posted - 2013-03-12 : 04:02:30
I need a query that looks like this:

the data are as follows:

MyTable (Col1, col2)

1 1
2 1
3 2
4 2
5 3

Col1 = Number used
Col2 = supervisor of col1

I need to know the supervisor of Number 5 and all superiors

Example, selecting 5, the result should be:
3, 2 and 1

3 is the supervisor of 5
2 is the supervisor of 3
1 is the supervisor of 2

Thank you for helping me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 05:17:53
[code]
DECLARE @MyTable table
(
Col1 int,
col2 int
)
insert @MyTable
values(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 3)

DECLARE @YourID int
SET @YourID = 5

;With Superiors
AS
(
SELECT Col1,Col2
FROM @MyTable
WHERE Col1 = @YourID

UNION ALL

SELECT t.Col1,t.Col2
FROM Superiors s
INNER JOIN @MyTable t
ON t.Col1 = s.Col2
WHERE t.Col1 <> t.Col2
)

SELECT Col2
FROM Superiors

OPTION (MAXRECURSION 0)



output
-----------------
Col2
-----------------
3
2
1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-03-12 : 07:00:03

thanks visakh16

it is ok
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 07:54:00
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -