sql server 2008 r2 - SQL retrieving tree structure query -

i have 2 tables

products table:

id     productname    category 0      t-shirt        15 1      aqua de gio    12 2      jacket         15 3      hot water      13 

categories table:

categoryid catagoryname       highercategoryid 8          fragnance          0 99         clothing           0 15         armani clothing    99 12         armani fragnance   8 102        davidoff fragnance 8 

expected result

id     productname    category  categorytree  0      t-shirt        15        clothing > armani cloting 1      aqua de gio    12        fragnance > armani fragnance 2      jacket         15        clothing > armani cloting 3      hot water      13        fragnance > davidoff fragnance 

for example take t-shirt products table

1. its category 15.
2. go categories table , see categoryid=15
3. look @ highercategoryid if = 0 stop, if not take value 99
4. look 99 in categoryid column, higher category 0 stop. based on above need "clothing > armani clothing".

i new sql queries , here first try

select       x.*,     x.p1 + isnull((' > ' + x.c1), '') + isnull((' > ' + x.c2), '') categorytree       (select           rp.categoryid catid,          rp.catagoryname    p1,          r1.catagoryname    c1,          r2.catagoryname    c2                categories rp      left outer join           categories r1 on r1.highercategoryid = rp.categoryid      left outer join           categories r2 on r2.highercategoryid = r1.categoryid       left outer join           categories r3 on r3.highercategoryid = r2.categoryid                 rp.highercategoryid != 0 ) x  

i not sure how stop joining when find 0 value in higher category, , how join products on categories, , there dynamic way without using lot of joins?

here goes:

with cte (catid, catname, highercatid) (   select categoryid, cast(catagoryname varchar(1000)), highercategoryid   categories   union   select c.categoryid, cast(cte.catname + ' > ' + c.catagoryname varchar(1000)), cte.highercatid   categories c   inner join cte on c.highercategoryid = cte.catid ) select p.id, p.productname, cte.catid, cte.catname cte inner join products p on (catid = p.category) cte.highercatid=0  

you got sqlfiddle here