I have 2 tables "Category & Item" .. In the first table "Category" .. I have a self join Relation.*The First Table Represent Main Categories which allow user to access items which are related to specific category*The Second Table Represent The Items which is related to each category*Each Category may be a child or parent for other categories--I want to know how can I get the full items count for specific category and his child items hierarchy tree .. (not only the items which is related to that category only .. we shall get the count for the items which related to his child also = Indirect Relation)

Examples : #The Items Count for "Electronic" Category will be 8#The Items Count for "TV" Category will be 4#The Items Count for "Cell Phones" Category will be 4#The Items Count for "LCD" Category will be 1#The Items Count for "LED" Category will be 1#The Items Count for "Smart Phone" Category will be 1#The Items Count for "3G Phone" Category will be 1

There might be several ways to go, but here's an option using a recursive query on a table-valued function. Before implementing this solution, be sure to understand what's going on and feel free to ask anything.DDL & Sample Data

ahmedhussein874 (10/30/2013)Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??

There might be, but it might take me a while to find something with good performance and I have work of my own. Is there any good reason to find a different way?

Luis, that's impressive. You essentially wrote the equivalent of Oracle's START WITH...CONNECT BY in SQL Server. As a bonus, you did it in a high-performance, compact and cool way. Seriously great work.

ahmedhussein874 (10/30/2013)Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??

There might be, but it might take me a while to find something with good performance and I have work of my own. Is there any good reason to find a different way?

Luis, that's impressive. You essentially wrote the equivalent of Oracle's START WITH...CONNECT BY in SQL Server. As a bonus, you did it in a high-performance, compact and cool way. Seriously great work.

A good inspiration moment got me a great compliment. Thak you, Ed.

Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?