Another Hierarchy question

[DanC]

I've got a table as follows:

subNo subName parent
1 National 1
2 VA 1
3 MD 1
4 Fairfax 2
5 Alexandria 2
6 Baltimore 3

I'm trying to create a query/procedure that will take an input variable (subNo) and then return all child subNo's in a temp table or table variable. I can't seem to wrap my head around the recursiveness that I need, since this will be an n-deep hierarchy. For example, if I pass in a value of '2', I need a table returned containing the following:

2
4
5


Any help at all would be appreciated.

 

[hneal98]

How does 2, 4 or 5 relate to a 2 being entered?

 

[DanC]

sorry, I guess it would have helped if I explained that part of it. If I pass in a 2, with 2 being the top level for this example, I need a result set consisting of 2, any subNo's with a parent = 2, any subNo's with a parent whose parent = 2 and so on.

 

[DanC]

well, I found one solution:

/* variable for input subNo */
declare @usrSub int
set @usrSub = 1

declare myCursor INSENSITIVE CURSOR FOR
select subNo
From subsetMaster
where parent = @usrSub
open myCursor

-- Create the local variables needed
declare @subNo varchar(50)

declare @sub table (
subNo int )

insert into @sub (subNo)
select @usrSub

-- Fetch the cursor
Fetch myCursor into @subNo

while @@fetch_status = 0
BEGIN

insert into @sub (subNo)
select subNo
from subsetMaster
where parent in (select subNo from @sub)
and subNo not in (select subNo from @sub)

Fetch myCursor into @subNo
END

close myCursor
deallocate myCursor

select * from @sub