T-Sql search by hierarchy

Question

I am using SQL Server.

I have a table Groups with two integer columns:

MEGR_KEY
MEGR_KEY1

MEGR_KEY is primary key of group. Each group can have sub groups.

For example - I have a group 1195:

MEGR_KEY = 1195

There are subgroups of 1195:

MEGR_KEY = 9484    
MEGR_KEY1 = 1195

and

MEGR_KEY = 7494
MEGR_KEY1 = 1195

Basically MEGR_KEY1 is telling, which group is a parent.

The problem I have is, how to find all MEGR_KEY subgroups hierarchically, given only root group name? Let's say (from previous example) there is 1195 root group. There are already two subgroups: 7494 and 9484. Now, those two subgroups can also be parent groups to some other groups. So, I have to find rows where MEGR_KEY1 = 7494 OR MEGR_KEY1 = 9484. How to find all subgroups if group number (MEGR_KEY) is given? I have a problem here writing query for this.

Take a look at recursive self-joins – rbr94 Nov 08 '16 at 11:45 — rbr94, Nov 08 '16 at 11:45

Shakeer Mirza · Accepted Answer · 2016-11-08T12:32:13.933

For your case lets build schema

    CREATE TABLE #TAB (MEGR_KEY INT, NAME VARCHAR(50), MEGR_KEY1 INT)

    INSERT INTO #TAB
    SELECT 19 , 'Name1'   , 0
    UNION ALL
    SELECT 20 ,'Name2'   , 19
    UNION ALL
    SELECT 21 , 'Name3'   , 20
    UNION ALL
    SELECT 22 , 'Name4'  , 21
    UNION ALL
    SELECT 23 , 'Name5'  , 21
    UNION ALL
    SELECT 26 , 'Name6'  , 19
    UNION ALL
    SELECT 28 , 'Name7'  , 0
    UNION ALL
    SELECT 29 , 'Name7'  , 18
    UNION ALL
    SELECT 30 , 'Name8'  , 18

Now Query the Table (I took a CTE recursively)

    DECLARE @MEGR_KEY INT=19;

    ;WITH CTE AS(
    SELECT  * FROM #TAB WHERE MEGR_KEY= @MEGR_KEY
    UNION ALL
    SELECT T.* FROM #TAB T 
    INNER JOIN CTE C ON T.MEGR_KEY1 = C.MEGR_KEY
    )
    SELECT * FROM CTE

And the result will be

T-Sql search by hierarchy

1 Answers1