Sql-server – Getting Indirect parents

ctequeryrecursivesql server

I have following different tables

LeafChilds

ChildId, ChildName

1, Child1

2, Child2

Group

GroupId, GroupName

1, Group1

2, Group2

3, All

GroupChildMapping

GroupId, ChildId

1, 1

2, 1

2, 2

GroupGroupMapping

GroupId, ParentGroupId

1, 3

1, 3

I am looking for query to find indirect parents for LeafChilds.
In above example – child1 is in Group1 and Group1 has parent group as ALL. So indirectly child1 is in ALL group as well.

OUTPUT

Child1, Group1

Child1, ALL

Best Answer

I believe the below will work for you. I made the final result return ID's rather than names so that you could see how to grab additional attributes from the base tables if necessary.

I also just realized that this only handles one level of group to parent, would need to slide in a recursive CTE after CTE_RawData to handle that scenario. Is that something your query needs to do? Or is one level of parent group enough?

/** The Setup - table variables, sample data **/

DECLARE @LeafChilds TABLE
    (
    ChildID INT NOT NULL PRIMARY KEY
    , ChildName NVARCHAR(100) NOT NULL
    )

INSERT INTO @LeafChilds
    (ChildID, ChildName)
VALUES (1, 'Child 1')
    , (2, 'Child 2')

DECLARE @Group TABLE
    (
    GroupID INT NOT NULL PRIMARY KEY
    , GroupName NVARCHAR(100) NOT NULL
    )

INSERT INTO @Group 
    (GroupID, GroupName)
VALUES (1, 'Group 1')
    , (2, 'Group 2')
    , (3, 'ALL')

DECLARE @GroupChildMapping TABLE
    (
    GroupID INT NOT NULL    --FK to @Group (GroupID)
    , ChildID INT NOT NULL  --FK to @LeafChilds (ChildID)
    )

INSERT INTO @GroupChildMapping
    (GroupID, ChildID)
VALUES (1, 1)
    , (2, 1)
    , (2, 2)

DECLARE @GroupGroupMapping TABLE
    (
    GroupID INT NOT NULL            --FK to @Group (GroupID)
    , ParentGroupID INT NOT NULL    --FK to @Group (GroupID)
    )

INSERT INTO @GroupGroupMapping
    (GroupID, ParentGroupID)
VALUES (1, 3)
    --, (1, 3)  --DUPLICATE PAIR??  

/** Get the Data 
    CTE_RawData - Gets the raw data
    CTE_UnPivot - unpivots the data and formats it as desired.
    **/
;WITH CTE_RawData AS
    (
    SELECT C.ChildID
        , C.ChildName
        , GM.GroupID AS DirectGroupID
        , GI.GroupName AS DirectGroupName
        , GMP.ParentGroupID AS IndirectGroupID
        , GP.GroupName AS IndirectGroupName
    FROM @LeafChilds AS C
        LEFT OUTER JOIN @GroupChildMapping AS CM ON CM.ChildID = C.ChildID
        LEFT OUTER JOIN @GroupGroupMapping AS GM ON GM.GroupID = CM.GroupID
        LEFT OUTER JOIN @Group AS GI ON GI.GroupID = GM.GroupID 
        LEFT OUTER JOIN @GroupGroupMapping AS GMP ON GMP.GroupID = CM.GroupID 
        LEFT OUTER JOIN @Group AS GP ON GP.GroupID = GMP.ParentGroupID
    WHERE GM.GroupID IS NOT NULL
    )
, CTE_UnPivot AS
    (
    SELECT ChildID
        , GroupInheritanceType 
        , GroupID 
    FROM (SELECT ChildID, DirectGroupID, IndirectGroupID FROM CTE_RawData AS R) AS C
        UNPIVOT (GroupID FOR GroupInheritanceType IN (DirectGroupID, IndirectGroupID)) AS unpvt
    )
SELECT P.ChildID
    , L.ChildName
    , P.GroupInheritanceType
    , P.GroupID 
    , G.GroupName
FROM CTE_UnPivot AS P
    LEFT OUTER JOIN @LeafChilds AS L ON L.ChildID = P.ChildID
    LEFT OUTER JOIN @Group AS G ON G.GroupID = P.GroupID
Related Question