Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Unanswered: Get Ancestor and Descendant in a Hierarchy

    For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about ‘Recursive CTEs’ in ‘SQL Server Books Online’, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.

    And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.

    First of all we have to create a table for the following functions and build some test data. The statemens look like:

    create table ST_CATEGORY(
    CATEGORYID uniqueidentifier not null default NEWID(),
    PARENTID uniqueidentifier,
    [NAME] varchar(128),
    COMMENT varchar(4096),
    CONSTRAINT PK_ST_CATEGORY primary key (CATEGORYID)
    )
    go

    insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
    values(@rootoid, NULL, 'ROOT', 'ROOT NODE')

    insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
    values(NEWID(), @rootoid, 'Business Application', 'group for all business applications')

    declare @techoid uniqueidentifier
    set @techoid = NEWID()

    insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
    values(@techoid, @rootoid, 'Tech101', 'technical tips')

    insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
    values(NEWID(), @techoid, 'C#', 'C# tips')
    go

    Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.

    CREATE PROCEDURE spGetAncestor
    @categoryID uniqueidentifier
    AS
    BEGIN
    -- find parents/ancestor
    WITH Ancestor( CATEGORYID ) AS
    (
    SELECT PARENTID
    FROM ST_CATEGORY
    WHERE CATEGORYID = @categoryID
    UNION ALL
    SELECT PARENTID
    FROM Ancestor, ST_CATEGORY
    WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID
    )
    SELECT * FROM Ancestor
    END
    GO

    CREATE PROCEDURE spGetSelfAndAncestor
    @categoryID uniqueidentifier
    AS
    BEGIN
    -- find self and parents/ancestor
    WITH SelfAndAncestor( CATEGORYID ) AS
    (
    SELECT CATEGORYID
    FROM ST_CATEGORY
    WHERE CATEGORYID = @categoryID
    UNION ALL
    SELECT PARENTID
    FROM SelfAndAncestor, ST_CATEGORY
    WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID
    )
    SELECT * FROM SelfAndAncestor
    END
    GO

    CREATE PROCEDURE spGetDescendant
    @categoryID uniqueidentifier
    AS
    BEGIN
    -- find children/descendant
    WITH Descendant( CATEGORYID ) AS
    (
    SELECT CATEGORYID
    FROM ST_CATEGORY
    WHERE PARENTID = @categoryID
    UNION ALL
    SELECT ST_CATEGORY.CATEGORYID
    FROM Descendant, ST_CATEGORY
    WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID
    )
    SELECT * FROM Descendant
    END
    GO

    CREATE PROCEDURE spGetSelfAndDescendant
    @categoryID uniqueidentifier
    AS
    BEGIN
    -- find self and children/descendant
    WITH SelfAndDescendant( CATEGORYID ) AS
    (
    SELECT CATEGORYID
    FROM ST_CATEGORY
    WHERE CATEGORYID = @categoryID
    UNION ALL
    SELECT ST_CATEGORY.CATEGORYID
    FROM SelfAndDescendant, ST_CATEGORY
    WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID
    )
    SELECT * FROM SelfAndDescendant
    END
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uhm.......OK. Thanks for sharing with us information that is available in Books Online....
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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