Help with a recursive call

Hi all,

I have a user table and a user_hierarchy table. The hierarchy table list children of the parent users.

ie
create user_hierarchy (
int parent_userid,
int child_userid
)

each parent can have any number of children and each child can in turn have children. so for users 1,2,3,4 you would have::

Parent,Child
1,2
1,3
1,4
1,5
2,3
2,4
3,5

I need a way to get all the children and children's children from a user.
so for user 1, it would give me back 2,3,4,5
for user 2 it would give back 3,4,5 (3 and 4 are direct children) and 5 is a child of 3

I have tried this using Temp tables and Cursors with recursion. The cursors won't work with the recursion and the temp table would be most problematic because this is a web application and I would need to generate unique temp table names for each call. Additionally, I can't seem to find an elegant way to get the children's children without implementing some sort of cursor or stepping algorithm.

Any help would be most appreciated.

Thanks In Advance,

billYou can try something like this:

SELECT INTO #results SELECT child_userid FROM user_hierarchy
WHERE Parent = @.parent
DECLARE @.next_level int
SELECT @.next_level = COUNT (*) FROM user_hierarchy
WHERE parent_userid IN ( SELECT * FROM #results )
AND child_userid NOT IN ( SELECT * FROM #results )

whle @.next_level != 0
Begin
SELECT INTO #results SELECT child_userid FROM user_hierarchy
WHERE parent_userid IN ( SELECT * FROM #results )
AND child_userid NOT IN ( SELECT * FROM #results )
SELECT @.next_level = COUNT (*) FROM user_hierarchy
WHERE parent_userid IN ( SELECT * FROM #results )
AND child_userid NOT IN ( SELECT * FROM #results )
End|||Hi

I have the same kind of problem. I don't know much about SQL.
I have a table parent_child that contains the following fields:

parent_child_id
parent_id
child_id
qty

I need to show the table in hierarchical form.

Parent Child
1 2
1 3
1 4
3 5
3 6

Needs to be displayed as (or as close to):

1 -> 2
-> 3 -> 5
-> 6
-> 4

Any ideas?

Thanks
Trav|||Assuming you have the following table in your database:

user_hierarchy (parent_userid int, child_userid int)

Add the next store proc and function to your database:

1) Function:

CREATE FUNCTION fnChilds (@.parent int, @.comp int, @.lev int)
RETURNS @.T1 TABLE (ParentID int, ChildID int, LevelNo int, DirectParent int)
AS
BEGIN
insert into @.T1 select @.parent,child_userid,@.lev,@.comp from user_hierarchy where parent_userid=@.comp
set @.lev=@.lev+1
declare Crs cursor for
select distinct ChildID from @.T1 order by 1
open Crs
fetch next from Crs INTO @.comp
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.T1 select ParentID, ChildID, LevelNo, DirectParent from dbo.fnChilds(@.parent,@.comp,@.lev)
fetch next from Crs INTO @.comp
END
close Crs
deallocate Crs
return
END

2) Store proc:

CREATE PROCEDURE GetChilds @.parent int AS
declare @.lev int,@.comp int
set @.lev=1
set @.comp=@.parent
select distinct * from dbo.fnChilds(@.parent,@.comp,@.lev) order by LevelNo

In Query Analyzer type:
Exec GetChilds 1 (where 1 is a valid code for a parent)

IONUT

Good look!|||Thanks heaps for that...

How do I display this though using ASP? Is it a matter of just calling the stored procedure or do I have to do something else to make it display the structure on the web?

I ultimately need to have a form that prompts a user for the parent no and then displays the structure for that parent.|||Worked out how to do my last question... Was pretty easy in the end.

However, how can I include the names of the parent and children in my results?

Thanks|||Assuming you have the following table in your database:

users(UserID int,UserName varchar(50))

, change the store proc to this one:

CREATE PROCEDURE GetChilds @.parent int AS
declare @.lev int,@.comp int
set @.lev=1
set @.comp=@.parent
select parent.UserName as ParentName,child.UserName as ChildName,LevelNo,directparent.UserName as DirectParentName
from (select distinct * from dbo.fnChilds(@.parent,@.comp,@.lev)) T
join users as parent on T.ParentID=parent.UserID join users as child on T.ChildID=child.UserID
join users as directparent on T.DirectParent=directparent.UserID
order by LevelNo,child.UserName

IONUT

PS In my opinion the introduction of functions (especially the functions that return a table) is a big step forward for SQLServer.

Beware, at first look, recursivity may seem like a cool thing, because this is exactly what SQL language was not (SQL statement treats all records that they processed as a whole, you can not interfear in the process to make recursive calls). It's true but, on the other hand recursive functions may give you a very strong headache because they are one of the best memory consumption agents, and can easily become a bottleneck for your application if they are used frequently and with large sets of records (to read: with many branches)|||Thanks once again... Now to really test you. Any ideas on how I could display this structure using ASP or something esle in hierarchical form instead of in a table.

It would be easier to read if you could see the branches.

eg.

Assembly 2
Part 1
Part 2
Assembly 1
Part 1

It doesn't have to be done using ASP ... I am just curious if anybody knows a way to show it like this. Even if there is a software package that will display it.

Thanks
Trav|||Just discovered an error... If I have any more than 2 levels I get the following error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Is there a way to have an infinite number of levels?

Cheers|||You can't overcome the 32 nested levels limit. Even so, I cant't figure out why it wasn't work afeter level 2?? So, I've changed the store proc and function with another store proc which is not recursive anymore:

Create this store proc in your database:

CREATE PROCEDURE GetChilds1
@.parent int AS
declare @.lvl int,@.cont int
declare @.T1 TABLE (ParentID int, ChildID int, LevelNo int, DirectParent int)
declare @.T2 TABLE (CompID int)
set @.lvl=0
insert into @.T2 values (@.parent)
set @.cont=1
while @.cont<>0
BEGIN
insert into @.T1
select @.parent,child_userid,@.lvl,tbl2.CompID from user_hierarchy tbl1
join @.T2 tbl2 on tbl1.parent_userid=tbl2.CompID
delete from @.T2
insert into @.T2 select distinct ChildID from @.T1 where LevelNo=@.lvl
set @.cont=@.@.rowcount
set @.lvl=@.lvl+1
END
select parent.UserName as ParentName,child.UserName as ChildName,LevelNo,directparent.UserName as DirectParentName
from @.T1 as T join users as parent on T.ParentID=parent.UserID join users as child on T.ChildID=child.UserID
join users as directparent on T.DirectParent=directparent.UserID
order by LevelNo,child.UserName

As for the layout in ASP script, that's your task to handle.

Good luck!

IONUT|||I discovered that I got the error because I added a child that already had the parent as one of its own children.

eg. parent = 4, children = 3, 2, 1

then I created a parent = 3 with children = 4, etc..

Is there an easy way to validate this when I insert records into the parent_child table? I don't have a stored procedure for the insert. I just have an insert statement that inserts a selected parent number into the parent column, and inserts the children as you go but doesn't allow you to insert the same child twice unless the parent number is different.

eg. I select parent = 4 and start adding children.

Parent Child
4 1
4 2
4 3

Sorry I don't know enough about writing stored procedures, functions, etc, and this is the only way I could come up with.|||It's more simple than to create store procedure or function. You simply decalre the primary key for the parent_child table as ParentID,ChildID. Or, if you already have another primary key for that table, you can declare an unique index on those two fields.

IONUT

PS Once you do that the only thing that you have to implement is a error check procedure (in your ASP code), in case that insert statement failed because of duplicates entries. (see the result that the execute method returns, for the command object that you used in vbscript)|||Sorry I don't really understand what you mean? I didn't think I could have a primary key in this parent_child table?

I need to be able to look all the way down a tree structure. Say I am inserting parent no = 6 with child no =3, but child no = 3 already exists as a parent with child no = 6.

It is okay to validate this but it gets tricky if parent no = 3 only contains child no = 4, but child no = 4 is a parent to child no = 6.

This is very confusing I know but I'd really appreciate any suggestions or stored procedures that will resolve this problem.

Thanks
Trav|||You can create a trigger (INSERTS and UPDATES) for your parentchild table, something like this:

if ((select count(*) from parent_child inner join inserted on parent_child.ParentID=inserted.ParentID and parent_child.ChildID=inserted.ChildID)+(select count(*) from parent_child inner join inserted on parent_child.ParentID=inserted.ChildID and parent_child.ChildID=inserted.ParentID))>0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION

ionut|||Thank... I created this trigger and it wouldn't allow me to do any inserts. I figured it was because the condition would not commit the transaction if the count > 0. So I changed it to count > 1 and this works fine, except I still have the problem that it will allow me to insert a child that is a parent which already contains this same parent some where down the line that I am inserting.

I have the following table:
P C
3 1
3 4
4 6

Then if I try to add item 6 as a parent with a child = item 3, I shouldn't be able to. It shouldn't let me do this because parent = 3 actually already contains item no 6 sitting under its other child no 4.

It doesn't matter how many branches down the tree I go, I shouldn't be able to add a child to a parent if that child already exists as a parent else where, and it contains this same parent I am creating as a child, grandchild, great grand child, etc, somewhere in its tree structure.

Sorry I am not explaining it very well... It is confusing.