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

i using sql 2012 , have self referencing table have 3 levels. table structure below:

table structure

i have table referencing table, have id foreign key, if foreign key table 6, need show 6 "aaa" , child node of "aa" child node of "a". need drill down lower level , lower level should able go upper level. can go second level.

below structure of table references other table.

so report on both these table , final output should :

if question not clear please ask try , clarify it.

assuming depth of category tree not more 3 levels, 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 @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 @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 depth max 3 levels  select * @customer cust     join (         select id, name categoryname, null categorytype, null subcategory @catergory roots parentid null         union         select mids.id, roots.name, mids.name, null @catergory mids             join @catergory roots on mids.parentid = roots.id , roots.parentid null         union         select leafs.id, roots.name, mids.name, leafs.name @catergory leafs             join @catergory mids on leafs.parentid = mids.id              join @catergory roots on mids.parentid = roots.id , roots.parentid null     ) struct on cust.categoryid = struct.id order cust.id 

output:

+----+---------+---------+----------+------------+----+--------------+--------------+-------------+ | id |  name   | surname | address  | categoryid | id | categoryname | categorytype | subcategory | +----+---------+---------+----------+------------+----+--------------+--------------+-------------+ |  1 | duck    | duffy   | 10 steet |         10 | 10 |            | aa           | null        | |  2 | ben     | ten     | 10 steet |          6 |  6 |            | aa           | aaa         | |  3 | cat     | dog     | 10 steet |          5 |  5 | c            | cc           | null        | |  4 | chicken | wings   | 10 steet |          1 |  1 |            | null         | null        | |  5 | fish    | water   | 10 steet |          7 |  7 | b            | bb           | null        | +----+---------+---------+----------+------------+----+--------------+--------------+-------------+ 

some columns still in there, i'm sure can rid of them. note though of cartegory columns have null values. because if customer in top or mid level category, there no reasonable way fill out columns.