Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Inserting into a hierachy (nested set model)

    Hello,

    I have a hierachy of groups modelled as a nested set. Like Joe Celko does it in his book "Trees and hierachies in SQL for smarties".

    I have a problem creating a query that will insert a new node at the rightmost position under a given parent node.

    If I have the tree:

    Code:
               node1
           /     |     \
         /       |       \
       /         |         \
    node2      node3     node4
      |
    node5
    I want to insert a new node6 here:

    Code:
               node1   
             
         /    |     |   \
        /     |     |    \        
       /      |     |     \
    node2   node3  node4  node6
       |
    node5
    In Celkos book, he gives a procedure that would add the new node, given the parent node (node1), as a sibling to node4. And another procedure that would add the new node to the right of a given sibling (node4). That approach is close to what I need, but I need to add it using the parent node.

    Anyone who has a procedure that can help me do that?

    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Joe Celko does it in his book "Trees and hierachies in SQL for smarties".
    I'm no smarty, so I haven't read his book.

    Can you give an example dataset before and after, so I can figure out what you want?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Basically, you will increment the right node of your parent node by 2 (along with all subsequent nodes of course), and then set the new child's left node to the parents old right node, and the child's right node to 1 higher than that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you happen to be using SQL Server 2008, why not use the hierarchyid?

  5. #5
    Join Date
    Mar 2009
    Posts
    2
    Quote Originally Posted by Wim View Post
    I'm no smarty, so I haven't read his book.

    Can you give an example dataset before and after, so I can figure out what you want?
    This is the data:
    Code:
    groupId   lft          rgt
    138	10	11
    140	12	13
    148	3	4
    802	9	14
    945	6	7
    963	4	5
    980	1	8


    Quote Originally Posted by blindman View Post
    Basically, you will increment the right node of your parent node by 2 (along with all subsequent nodes of course), and then set the new child's left node to the parents old right node, and the child's right node to 1 higher than that.
    Can you help me modify my existing procedure (by Joe Celko), to do that? I can't get it working. Another problem that I ran into when trying to solve it, is when the note given as parent node has no childs (and therefor not really a parent node), then it should become a parent node, with the new node as a the only child.

    Here is Joe Celko's SP (which he credit to Mark E. Barney), that will insert the new node as a child to the rightmost child of the parent node:
    Code:
    CREATE PROCEDURE [dbo].[InsertNewNode] (
    	@new_group int, @parent_group int)
    AS
    
    DECLARE @parent int
    SET @parent = (SELECT rgt
    				FROM ActiveDirectory.GroupRelTest
    				WHERE groupId = @parent_group)
    
    UPDATE ActiveDirectory.GroupRelTest
    	SET lft = CASE WHEN lft > @parent
    				THEN lft + 2
    				ELSE lft END,
    		rgt = CASE WHEN rgt >= @parent
    				THEN rgt + 2
    				ELSE rgt END
    WHERE rgt >= @parent
    
    INSERT INTO ActiveDirectory.GroupRelTest (groupId, lft, rgt)
    VALUES (@new_group, @parent, (@parent + 1))
    Any help will be much appreciated.



    Quote Originally Posted by MCrowley View Post
    If you happen to be using SQL Server 2008, why not use the hierarchyid?
    Unfortunately, I'm using SQL Server 2005.

Posting Permissions

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