Since the query above resolves out children from parents, you first have to resolve the parents of the groups you want to pass in:
;WITH
Groups AS (
SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
FROM dbo.[group] g
LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
),
Parents AS (
SELECT GroupId, GroupName, ParentGroupId
FROM Groups
WHERE GroupName in ('B2')
UNION ALL
SELECT g.GroupId, g.GroupName, g.ParentGroupId
FROM Groups g
INNER JOIN Parents lr ON lr.ParentGroupId = ga.GroupId
) SELECT * FROM Parents
This returns B2 (the group passed in), B (parent of B2), and A (parent of B). Now, you want all associations - upstream and downstream, so we use the output from this query as the anchor part of the generic rCTE I posted yesterday. You could stream the result from the rCTE "Parents" into a temp table and use it as a feed:
;WITH
Groups AS (
SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
FROM dbo.[group] g
LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
)
, rCTE AS (
SELECT [Level] = 1, g.GroupId, g.GroupName, g.ParentGroupId -- anchor part
FROM Groups g
INNER JOIN #Parents p ON p.GroupId = g.GroupId
UNION ALL
SELECT [Level] = [Level] + 1, tr.GroupId, tr.GroupName, tr.ParentGroupId -- recursive part
FROM Groups tr
INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
WHERE lr.[Level] < 8
)
SELECT DISTINCT GroupId, GroupName
FROM rCTE
Alternatively, you could chain them all together like this:
;WITH
Groups AS (
SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
FROM dbo.[group] g
LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
),
Parents AS (
SELECT GroupId, GroupName, ParentGroupId--, ParentGroupName
FROM Groups
WHERE GroupName in ('B2')
UNION ALL
SELECT ga.GroupId, ga.GroupName, ga.ParentGroupId--, ga.ParentGroupName
FROM Groups ga
INNER JOIN Parents lr ON lr.ParentGroupId = ga.GroupId
),
rCTE AS (
SELECT [Level] = 1, g.GroupId, g.GroupName, g.ParentGroupId -- anchor part
FROM Groups g
INNER JOIN Parents p ON p.GroupId = g.GroupId
UNION ALL
SELECT [Level] = [Level] + 1, tr.GroupId, tr.GroupName, tr.ParentGroupId -- recursive part
FROM Groups tr
INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
WHERE lr.[Level] < 8
)
SELECT DISTINCT GroupId, GroupName
FROM rCTE
Results:
GroupIdGroupName
1A
2B
3B1
4B2
5B101
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]