SQL query recursion for a web-like structure
- by MickeyD
I have a table here, named "Foo". The data is set up something like this.
ID TableReference DataId0 DataId1 DataId2
-- -------------- ------- ------- -------
1 Prize 3 4 5
2 Prize 4 5 NULL
3 Cash 1 NULL NULL
4 Prize 8 NULL 12
5 Foo 2 3 NULL
6 Cash 8 1 10
7 Foo 5 1 2
Etc. The data is horribly set up, I know, but I didn't set it up that way. :) I'm only dealing with the after effect.
I'm trying to come up with a way to essentially "flatten" the table; that is, to display all the data to a point where the table "Foo" does not reference itself. I'm trying to figure out a sql query that I can do to get there.
Usually when I deal with recursion, I have (or can establish) parent IDs and set it up that way, but for this table there are seemingly multiple child and parent IDs creating a web-like structure instead of a hierarchy. So I'm at a loss where to even begin to write a sql query for something like this.
Note: There is no infinite looping (where one Foo points to another Foo, which points back to the original Foo) from what I've found.
Using t-sql. Thanks for any assistance, if at all possible.
