Recursive Query Help

Question

I have two tables in my database schema that represent an entity having a many-to-many relationship with itself.

Role  
---------------------
+RoleID 
+Name

RoleHasChildRole
---------------------
+ParentRoleID  
+ChildRoleID  

Essentially, I need to to be able to write a query such that:

Given a set of roles, return the unique set of all related roles recursively.

This is an MSSQL 2008 Database.

EDIT:

A request for some sample data was required. So here goes:

RoleID    Name
------------------------------------
1         'Admin'
2         'SuperUser'
3         'Lackey'
4         'Editor'
5         'CanEditSomething'
6         'CanDeleteSomething'
7         'CanCreateSomething'
8         'CanViewSomething'

ParentRoleID    ChileRoleID
------------------------------------
1               5
1               6
1               7
1               8
2               4
4               5
4               8

So a query for the Admin role would return:

'Admin'
'CanEditSomething'
'CanDeleteSomething'
'CanCreateSomething'
'CanViewSomething'

And a query for SuperUser would return:

'SuperUser'
'Editor'
'CanViewSomething'
'CanEditSomething'

Answer 1

My QCD (Quick,cheap and dirty) example:

Assume a simple employee manager relationship in an organization. We are going to have a EmployeeManager Table, EmpMan, which has 2 columns EmpID and ManID. I am going to leave out the other details (indexes, secondary tables which have employee name/contacts etc) for the sake of simplicity

CREATE TABLE [dbo].[EmpMan]( [EmpID] [int] NOT NULL, [ManID] [int] NOT NULL) GO;

   insert into dbo.EmpMan   select 2,1

union select 3,1
union select 4,1 
union select 31,3 
union select 32,2
union select 43,4   `/* 3X report to 3 and 4X report to 4*/`

union select 310,31 
union select 314,31 `/* 31X reports to 31*/`

union  select 56,5 union select 87,8 `/*empID 56 reports to 5 and 87 reports to  8, 5 and 8 do not have managers*/`

CTE query can do recursive queries:

with Manager AS (

/*initialization query*/

select  EmpID,ManID from EmpMan where ManID=1/* assuming that your VP ID is 1, or it can be the top most person whom you want to query on*/

union all

/*recursive query*/

select E.EmpID,E.ManID from EmpMan E

join Manager M on  E.ManID=M.EmpID)

select * from Manager

Answer 2

Pretty common CTE usage:

WITH RecursiveRole AS (
  SELECT RoleID AS RecursiveRoleID
  FROM Role
  WHERE Name = @parameter

  UNION ALL

  SELECT ChildRoleID AS RecursiveRoleID
  FROM RoleHasChildRole
  INNER JOIN RecursiveRole
    ON RoleHasChildRole.ParentRoleID = RecursiveRole.RecursiveRoleID
)
SELECT RoleID, RoleName
FROM RecursiveRole
INNER JOIN Role
  ON RecursiveRoleID = RoleID

This one only goes down the role tree. I leave making one that goes up as an exercise.

---

EDIT Looks like you only wanted to go down the tree anyway. This query does that just fine.

Returns the following results on your test data:

SET @parameter = 'Admin'
1   Admin
5   CanEditSomething
6   CanDeleteSomething
7   CanCreateSomething
8   CanViewSomething

SET @parameter = 'SuperUser'
2   SuperUser
4   Editor
5   CanEditSomething
8   CanViewSomething