sql - get all nested children for a parent id -

started fiddling

work table productid, labelname, categoryid, childcategoryid ------------------------------------ 1, widget a, 1, null null, category a, 2, 1  2, widget b, 3, null  categories table categoryid, categoryname --------------------------- 1, category 2, category b 3, category c 

given information above, how categories product id?

for example, given product id of 1, following desired results.

desired results productid, labelname, categoryid, childcategoryid ------------------------------------ 1, widget a, 1, null null, category a, 2, 1  null, category b, null, 2 

it supposed hierarchical data , apologize not being able explain well. boggling mind. widget has product id of 1 , category id of 1. means records have childcategoryid of 1 included, gives category a. cata has category id of 2, before, records have childcategoryid of 2 included in result, why category b included.

this mess produces sample result sample data. still isn't clear you think algorithm ought be.

declare @categoryitems table (   categoryname nvarchar(255),   label nvarchar(255),   productid int,   childcategoryid int,   categoryid int );  declare @categories table (   categoryid int,   name nvarchar(100) );  insert @categoryitems ( categoryname, label, productid, childcategoryid, categoryid ) values   ( 'categorya', 'widget a', 1, 0, 1 ),   ( 'categoryb', 'categorya', 0, 1, 2 ),   ( 'categoryc', 'widget b', 2, 0, 3 ); insert @categories ( categoryid, name ) values   ( 1, 'categorya' ),   ( 2, 'categoryb' ),   ( 3, 'categoryc' );  select * @categories; select * @categoryitems;  declare @targetproductid int = 1;  leonard (   -- start target product.   select 1 [row], productid, label, categoryid, childcategoryid     @categoryitems     productid = @targetproductid   union   -- add each level of child category.   select l.row + 1, null, ci.label, ci.categoryid, ci.childcategoryid     @categoryitems ci inner join       leonard l on l.categoryid = ci.childcategoryid ),   gertrude (     -- take makes sense.     select row, productid, label, categoryid, childcategoryid       leonard     union     -- tack on row measure.     select l.row + 1, null, c.name, null, c.categoryid       leonard l inner join         @categories c on c.categoryid = l.categoryid       l.row = ( select max( row ) leonard ) )   select row, productid, label, categoryid, childcategoryid     gertrude     order row; 

i suspect problem have mixed data in lopsided manner. hierarchy of categories usally represented like:

declare @categories table (   categoryid int identity,   category nvarchar(128),   parentcategoryid int null ); 

the root of each hierarchy indicated parentcategoryid null. allows number of independent trees coexist in single table , not depend on existence of products.

if products assigned single (sub)category include categoryid in products table. if product may assigned several (sub)categories, possibly in different hierarchies, use separate table relate them:

declare @productcategories table (   productid int,   categoryid int );