Recusion on Group and their subGroups- how can I solve this problem?

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 13, 2012 at 12:18 am

  #1433035

  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

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]