Hello,
I have a table of many rows, and some rows have a connection to other rows. A simplified example of the table I am trying explain is below.
ID MYVar LinkedToMYVar
1 10 0
2 20 0
3 30 10
4 40 0
5 50 10
6 60 30
7 70 30
8 80 0
9 90 50
10 100 0
11 110 70
12 120 0
First, I start with MYVar=10
Then, I want to get all rows where LinkedToMyVar=MYVar(in this case 10)
something like: Select MyVar where LinkedToMyVar=@MyVarofLast (in this case returns 2 rows of MYVar=30, 50)
Next, I want to find all rows that have a LinkedToMyVar= the value of MyVar of each new row above. In this example it would be all rows where LinkedToMyVar=30 and LinkedToMyVar=50 which would return MyVar=60,70 and 90
Next I want to select MyVar from every row where LinkedtoMyvar=60, 70 or 90
I want to keep doing this until there are no LinkedToMyVar equal to my last set of Myvars
To sum it up, I start with a MyVar of 10.
Then I want to select MyVar from each row with LinkedToMyVar=@MyVar(=10)(returns row 3 and 5)
Then I want to select MyVar from each row with LinkedToMyVar=@Myvar(=30 and 50)(returns row 6, 7, and 9)
Then Select MyVar from each row with LinkedToMyVar=@Myvar(=60, 70, 90)(returns row 11)
Then Select MyVar from each row with LinkedToMyVar=@Myvar(=110) (returns 0 rows and ends the select statement)
Is it possible to create a while loop to do something as such? The goal is to get a count of all the MyVars I can fetch(this example would be 6).
I hope I clarified that good enough. I couldn't find a good way to explain what I'm trying to do. If you can help, Thanks!