sql server - T-Sql search by hierarchy -

this question has answer here:

• select statement return parent , infinite children 2 answers
• simplest way recursive self-join? 4 answers

i using sql server.

i have table groups 2 integer columns:

megr_key megr_key1 

megr_key primary key of group. each group can have sub groups.

for example - have group 1195:

megr_key = 1195

there subgroups of 1195:

megr_key = 9484     megr_key1 = 1195 

and

megr_key = 7494 megr_key1 = 1195 

basically megr_key1 telling, group parent.

the problem have is, how find megr_key subgroups hierarchically, given root group name? let's (from previous example) there 1195 root group. there 2 subgroups: 7494 , 9484. now, 2 subgroups can parent groups other groups. so, have find rows megr_key1 = 7494 or megr_key1 = 9484. how find subgroups if group number (megr_key) given? have problem here writing query this.

for case lets build schema

    create table #tab (megr_key int, name varchar(50), megr_key1 int)      insert #tab     select 19 , 'name1'   , 0     union     select 20 ,'name2'   , 19     union     select 21 , 'name3'   , 20     union     select 22 , 'name4'  , 21     union     select 23 , 'name5'  , 21     union     select 26 , 'name6'  , 19     union     select 28 , 'name7'  , 0     union     select 29 , 'name7'  , 18     union     select 30 , 'name8'  , 18 

now query table (i took cte recursively)

    declare @megr_key int=19;      ;with cte as(     select  * #tab megr_key= @megr_key     union     select t.* #tab t      inner join cte c on t.megr_key1 = c.megr_key     )     select * cte 

and result be