Recursive Query Help

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'

'CanEditS开发者_如何学Comething'

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

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