Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Unanswered: Transitive closure

    For ex.
    If i have this table

    (Id*,Description, ParentId)

    And given an Id i want to know all his childrens, and the childres of the childrens and ...

    How can i do this with SQL?

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Create a user-defined function that takes @ID as a parameter and returns a table including that ID and all children IDs.

    Here is a rough code outline for the function:

    Declare @Progeny table (ID)

    Insert into @Progeny (ID) select @ID

    While @@RowCount > 0
    insert into @Progency (ID)
    select YourTable.ID
    from @Progeny Progeny
    inner join YourTable on Progeny.ID = YourTable.ParentID
    where not exists (select * from @Progeny CurrentIDs where YourTable.ID = CurrentIDs.ID)

    You could also do this in a procedure, but it is convenient as a table-function because you can then easily use it for filtering other datasets.


Posting Permissions

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