How to get child data from a self referencing table in sql

Question

I am using sql 2012 and I have a self referencing table that have 3 levels. The table structure is as below:

Table Structure

I have another table that is referencing this table, it have ID as a foreign key, so if the foreign key to that table is 6, I need to show that 6 is "AAA" and it is the child node of "AA" which is the child node of "A". I need to drill down up to the lower level and from the lower level I should be able to go up to the upper level. Currently I can go up to the second level.

Below is the structure of the table that references the other table.

So I would like to report on both these table and the final output should look like this :

If my question is not too clear please ask I will try and clarify it.

Possible duplicate of [CTE Recursion to get tree hierarchy](http://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy) — iamdave, Feb 01 '17 at 13:36
Why don't you use `hierarchyid` ? Is the number of levels fixed? If it is, you don't need recursion or CTEs, just 3 self joins. The code will be simpler and performance a lot faster than the CTE — Panagiotis Kanavos, Feb 02 '17 at 09:52
not clear about `CategoryName`, `CategoryType` and `SubCategory`. what is the `Type`? — anatol, Feb 02 '17 at 09:58
That is the CategoryName child, if Category Name is A the CategoryType is AA SubCategory will be AAA, I am sorry for the bad naming. — Tooleh, Feb 02 '17 at 10:03

score 1 · Accepted Answer · answered Feb 02 '17 at 10:16

Assuming that the depth of the category tree is not more than 3 levels, this should work:

declare @Catergory table (
    ID int not null,
    Name nvarchar(10) not null,
    ParentID int null
)
declare @Customer table (
    ID int not null,
    Name nvarchar(10) not null,
    SurName nvarchar(10) not null,
    Address nvarchar(30) not null,
    CategoryId int not null
)
insert into @Catergory (ID, Name, ParentID)
values (1, 'A', null), (2, 'B', null),
    (3, 'C', null), (4, 'AA', 1),
    (5, 'CC', 3), (6, 'AAA', 4),
    (7, 'BB', 2), (8, 'AAA', 4),
    (9, 'CCC', 5), (10, 'AA', 1)

insert into @Customer (ID, Name, SurName, Address, CategoryId)
values (1, 'Duck', 'Duffy', '10 Steet', 10),
 (2, 'Ben', 'Ten', '10 Steet', 6),
 (3, 'Cat', 'Dog', '10 Steet', 5),
 (4, 'Chicken', 'Wings', '10 Steet', 1),
 (5, 'Fish', 'Water', '10 Steet', 7)

-- build structure using assumption that the depth is max three levels

select *
from @Customer cust
    join (
        select ID, Name as CategoryName, null As CategoryType, null as SubCategory from @Catergory roots where ParentID is null
        union
        select mids.ID, roots.Name, mids.Name, null from @Catergory mids
            join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
        union
        select leafs.ID, roots.Name, mids.Name, leafs.Name from @Catergory leafs
            join @Catergory mids on leafs.ParentID = mids.ID 
            join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
    ) as struct on cust.CategoryId = struct.ID
order by cust.id

Output:

+----+---------+---------+----------+------------+----+--------------+--------------+-------------+
| ID |  Name   | SurName | Address  | CategoryId | ID | CategoryName | CategoryType | SubCategory |
+----+---------+---------+----------+------------+----+--------------+--------------+-------------+
|  1 | Duck    | Duffy   | 10 Steet |         10 | 10 | A            | AA           | NULL        |
|  2 | Ben     | Ten     | 10 Steet |          6 |  6 | A            | AA           | AAA         |
|  3 | Cat     | Dog     | 10 Steet |          5 |  5 | C            | CC           | NULL        |
|  4 | Chicken | Wings   | 10 Steet |          1 |  1 | A            | NULL         | NULL        |
|  5 | Fish    | Water   | 10 Steet |          7 |  7 | B            | BB           | NULL        |
+----+---------+---------+----------+------------+----+--------------+--------------+-------------+

Some extra columns are still in there, but I'm sure you can get rid of them. Note though that some of the cartegory columns have null values. This is because if a customer is in a top or mid level category, there is no reasonable way to fill out those columns.

Thank you so much, I have been trying to archive this for days. you are a star. It is working perfectly. — Tooleh, Feb 02 '17 at 11:45

How to get child data from a self referencing table in sql

1 Answers1