-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[TransitiveClosure]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DELETE FROM related_topics

-- Make all topics relate to themselves
    INSERT related_topics
        SELECT  
            doc_id AS ancestor_id,
            doc_id AS descendant_id
        FROM document
        WHERE
            document.doc_type = 'Topic'

-- seed table with all of the parent
-- child pairs.
    INSERT  related_topics
        SELECT DISTINCT
            parent_id AS ancestor_id,
            doc_id AS descendant_id
        FROM outline
        WHERE
            parent_id IS NOT NULL


    -- Compute the closure
    WHILE @@ROWCOUNT > 0
    BEGIN
        INSERT  related_topics
        SELECT DISTINCT outleft.ancestor_id, outright.descendant_id
        FROM
        related_topics AS outleft
        JOIN
        related_topics AS outright
        ON
        outleft.descendant_id = outright.ancestor_id
        
        LEFT JOIN

        related_topics AS duplist

        ON
        duplist.ancestor_id = outleft.ancestor_id AND
        duplist.descendant_id = outright.descendant_id
    
        WHERE
        duplist.ancestor_id IS NULL
    END

    -- Remove entries that don't relate to topics
    DELETE FROM related_topics  
    FROM document
    JOIN related_topics
    ON document.doc_id = related_topics.descendant_id
    WHERE document.doc_type != 'Topic'
END
GO