USE [UM3_kosuch]
GO

/****** Object:  View [dbo].[serie_family]    Script Date: 17.10.2019 10:40:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[serie_family] (
    ancestor_serie_id, descendant_serie_id, distance
)AS
WITH transitive AS (
        SELECT parent_serie_id AS ancestor_serie_id, child_serie_id AS descendant_serie_id, 1 AS distance
        FROM dbo.serie_parent_child
    UNION ALL
        SELECT spc.parent_serie_id AS ancestor_serie_id, t.descendant_serie_id, t.distance + 1
        FROM dbo.serie_parent_child spc
        JOIN transitive t ON t.ancestor_serie_id = spc.child_serie_id
)
SELECT ISNULL(ancestor_serie_id, '00000000-0000-0000-0000-000000000000'), ISNULL(descendant_serie_id, '00000000-0000-0000-0000-000000000000'), ISNULL(distance, 0) FROM transitive;
GO