Getting Indirect parents 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
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