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.
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 12 13 24 25 3Col1 = Number usedCol2 = supervisor of col1I need to know the supervisor of Number 5 and all superiorsExample, selecting 5, the result should be:3, 2 and 13 is the supervisor of 52 is the supervisor of 31 is the supervisor of 2Thank 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 @MyTablevalues(1, 1),(2, 1),(3, 2),(4, 2),(5, 3)DECLARE @YourID intSET @YourID = 5;With SuperiorsAS(SELECT Col1,Col2FROM @MyTableWHERE Col1 = @YourIDUNION ALLSELECT t.Col1,t.Col2FROM Superiors sINNER JOIN @MyTable tON t.Col1 = s.Col2WHERE t.Col1 <> t.Col2)SELECT Col2FROM SuperiorsOPTION (MAXRECURSION 0)output-----------------Col2-----------------321[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-03-12 : 07:00:03
|
thanks visakh16it is ok |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 07:54:00
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|