SQL Server Return All Sub Categories -

i have 2 tables in sql server 2008 r2:

tblcategories

• cat_id
• cat_name

tblcategoryhierarchy

• cat_parent_id
• cat_child_id

there can number of levels, , number of sub-categories under each category. changing table structure not possible.

what i'm looking do, provide 1 cat_id , return cat_id's under within hierarchy, no matter how many levels there are.

for example (tblcategoryhierarchy):

cat_parent_id cat_child_id 101 200 101 201 101 202 101 203 202 300 202 301

• 100
• 101
  • 200
  • 201
  • 202
    • 300
    • 301
  • 203
• 102

if provide cat_id 101, need return: 200, 201, 202, 300, 301 , 203.

i've attempted vb had lots , lots of queries , slow. i'm looking pure sql solution, i'm hoping fast , not take many server resources.

a store procedure sounds me, there way achieve need way..?

you can recursive common-table expression (cte).

something below should it. here x = 4 a
constant: input cat_id (200 in example).

with catcte (cat_id) (     select t.cat_id     tblcategories t     t.cat_id = 4      union      select p.cat_child_id cat_id     catcte m     join tblcategoryhierarchy p on m.cat_id = p.cat_parent_id  ) select cat_id catcte cat_id <> 4; 

script creates testing data:

create table tblcategories(cat_id int, cat_name varchar(20));  create table tblcategoryhierarchy(cat_parent_id int, cat_child_id int);  insert tblcategories(cat_id, cat_name) values ( 1, 'cat 1'); insert tblcategories(cat_id, cat_name) values ( 2, 'cat 2'); insert tblcategories(cat_id, cat_name) values ( 3, 'cat 3'); insert tblcategories(cat_id, cat_name) values ( 4, 'cat 4'); insert tblcategories(cat_id, cat_name) values ( 5, 'cat 5');  insert tblcategories(cat_id, cat_name) values ( 6, 'cat 6'); insert tblcategories(cat_id, cat_name) values ( 7, 'cat 7'); insert tblcategories(cat_id, cat_name) values ( 8, 'cat 8'); insert tblcategories(cat_id, cat_name) values ( 9, 'cat 9'); insert tblcategories(cat_id, cat_name) values (10, 'cat 10');  insert tblcategories(cat_id, cat_name) values (11, 'cat 11'); insert tblcategories(cat_id, cat_name) values (12, 'cat 12');  insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 1, 2); insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 1, 3);  insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 4, 6); insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 4, 8);  insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 8, 10); insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 8, 11);  insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values (11, 12);  insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 5, 7); insert tblcategoryhierarchy (cat_parent_id, cat_child_id) values ( 5, 9);