Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2002
    Posts
    6

    Unanswered: 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,

    bill

  2. #2
    Join Date
    Mar 2002
    Location
    Egersund, Norway
    Posts
    4
    You 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

  3. #3
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8

    Re: Help with a recursive call

    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

  4. #4
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Help with a recursive call

    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!

  5. #5
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8

    Help with a recursive call

    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.

  6. #6
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8

    Smile

    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

  7. #7
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    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)

  8. #8
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8
    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

  9. #9
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8
    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

  10. #10
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    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

  11. #11
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8

    Red face

    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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Child
    4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;1
    4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;2
    4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;3

    Sorry I don't know enough about writing stored procedures, functions, etc, and this is the only way I could come up with.

  12. #12
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    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)

  13. #13
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8

    Unhappy

    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

  14. #14
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    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

  15. #15
    Join Date
    Mar 2003
    Location
    Melbourne
    Posts
    8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •