sql - How to get all parent and child categories that each product belongs to? -

i have 3 tables:

create table [dbo].[productcategory](  [categoryid] [int] identity(1,1) not null,  [categoryparentid] [int] null,  [categoryname] [nvarchar](200) not null )  create table [dbo].[product] (  [productid] [bigint] identity(1,1) not null,  [productname] [nvarchar(100)] not null,  .  . )  create table [dbo].[producttocategorylink] (  [productid] [bigint] not null,  [categoryid] [int] not null ) 

sample data example:

product:

1, 'book_1';

2, 'book_2';

productcategory:

1, null, 'books';

2, 1, 'books subcategory lvl_1';

3, 2, 'books subcategory lvl_2';

4, 3, 'books subcategory lvl_3';

producttocategorylink:

1, 4;

2, 2;

question: how parent , child categories each product belongs to?

so, need this:

---

productid, productname, categoryid, categoryname

1, 'book_1', 1, 'books';

1, 'book_1', 2, 'books subcategory lvl_1';

1, 'book_1', 3, 'books subcategory lvl_2';

1, 'book_1', 4, 'books subcategory lvl_3';

2, 'book_2', 1, 'books';

2, 'book_2', 2, 'books subcategory lvl_1';

the sql query exhibited @ bottom of post should trick, if verbosely.

the key feature recursive definition of hierarchy table expression. first select retrieves product-category links, along names , category parent ids. base case of recursion. second select finds of category parent rows of rows found in previous step. note particularly second select joins hierarchy expression again, making query recursive. sql server repeatedly evaluate second select until no new records found. third select statement returns results.

as might expect, bad thing if there cycles in category parent chain. sql server limits number of times recurse before abandons query. default limit 100, enough unless category hierarchy outrageously deep. if necessary, can raise as 32767 using maxrecursion query option.

here sql query:

with   hierarchy (     select       prod.productid     , prod.productname     , cat.categoryid     , cat.categoryparentid     , cat.categoryname     dbo.producttocategorylink link     inner join dbo.product prod       on prod.productid = link.productid     inner join dbo.productcategory cat       on cat.categoryid = link.categoryid     union     select       child.productid     , child.productname     , parent.categoryid     , parent.categoryparentid     , parent.categoryname     hierarchy child     inner join dbo.productcategory parent       on parent.categoryid = child.categoryparentid   ) select   productid , productname , categoryid , categoryname hierarchy order   productid , categoryid