FrenkyB FrenkyB - 26 days ago 13
SQL Question

T-Sql search by hierarchy

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.

Answer

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

enter image description here

Comments