Complicated nested query (or stored procedure ?) 1

[pharcyder]

Hello,

I don't know how to solve this problem purely with SQL or a stored procedure.

There is a table called tlb_Users looking like this:
[tt]
UserID SuperiorID
1 1
2 1
3 2
4 3
[/tt]

What I need is quite simple:
I want to know all the inferiors of one User (and himself).
e.g. the UserID is "2" the output should be "2", "3", "4" (4 because his superior is 3 who is inferior of 2).

Simpler example is UserID 3, where only "4" and "3" is the output.

Problem is that there could be more and more "nested" IDs
by adding new users with superiors which are inferiors of another superior and so on.

Is there a posibility to do this by SQL or do I need a stored procedure for this task ?

Or is there even a simpler possibility to solve this problem ?

Thanks in advance,
Jens

 

[AWithers]

I think you do need an sp

see 'expanding hierarchies' in books on line

Andy

 

[pharcyder]

Thanks Andy,
I found some code via the keyword you gave me.

Here's the code I am now using (modified to print out with commas, not indented):

[tt]
CREATE PROCEDURE sp_expand (@current nvarchar(20)) as
SET NOCOUNT ON
DECLARE @lvl int, @line nvarchar(40)

CREATE TABLE #stack (item char(20), lvl int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @lvl = 1
Select @line = ''
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item
FROM #stack
WHERE lvl = @lvl

set @line = @line + rtrim(@current) + ','


DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current

INSERT #stack
SELECT UserID, @lvl + 1
FROM tbl_Users
WHERE SuperiorID = @current

IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE
PRINT left(@line, len(@line) - 1)
[/tt]

Greetings from Germany,
Jens