SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 recursive query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wided
Posting Yak Master

201 Posts

Posted - 03/12/2013 :  04:02:30  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/12/2013 :  05:17:53  Show Profile  Reply with Quote

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



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

Edited by - visakh16 on 03/12/2013 05:22:27
Go to Top of Page

wided
Posting Yak Master

201 Posts

Posted - 03/12/2013 :  07:00:03  Show Profile  Reply with Quote

thanks visakh16

it is ok
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/12/2013 :  07:54:00  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000