Do I need some sort of recursive query?

I have a problem that is probably rather well known in the MLM software industry.

I have a table of users called (surprise) Users, which amongst other columns, has an ID column.

I have a linking table called User_Uplines, which contains the foreign keys FK_UserID (which points to the Users.ID column of the User), and FK_UplineID (which points to the Users.ID column of the User's Upline Sponsor).

So, the query:

Select * from User_Uplines where FK_UplineID = @UserID

will return all of a user's FIRST LEVEL subordinates.

The problem is, I'd like to return ALL of the User's downlines' downlines, traversing every branch, until there are no more. I could certainly do this with a (rather nasty) cursor, or bring the recursive function into C# and make a BUNCH of db calls to get the first level of each user, and recursive through a collection until I reach the last entry. BUT, is there any way to write a recursive routine in SQL that would do this? I'm using Microsoft SQL 2000.

Many thanks in advance, and if you need any more info, please let me know.

Thanks,

Bill

Well, this is definitely not just something encountered in the MLM world. Anyone using any type of tree structure like file systems or organization charts has to deal with it. The best way I've found to deal with it is to just think of what you are going to do with the information once you get it. There is no way to effectively display this information to a user; there just isn't enough screen space.

What it comes down to, though is that there is no easy way to get ALL of the information out of the database, but if you think about what your end result should look like, you might be able to find an easy way to get that PIECE of the data out.

What I usually do is have one table of users with that user's information and a UID column. Then I add a linking column (sponserID in your case) which contains the UID of the person's sponser. This way you can contain all of the information in one table. You still have to itterate through it to traverse the connections, though.

Now, lets say you want to get a list of all of a given user's downline email addresses: Select uid,email from users where sponserid = (this user's uid) gives you the first line, then add these to the list and recurse to get the rest.

Thanks, that's exactly what I am doing (as I stated in my original post). However, I was looking for a way to recurse the call via SQL.

BTW... since I only need 10 levels, I simply added 9 more columns to the linking table (Upline1ID through Upline10ID), and on an insert, I simply copy these from the user's upline, and shift them by one (i.e, Upline1ID becomes Upline2ID, etc). Then, I can do a select * from table
where
Upline1ID = @ID
OR Upline2ID = @ID...

down to Upline10ID = @ID

That gets me the entire downline. And the final use was to populate a drop down control, so that the user can select anyone in his/her downline.

Bill