Hi all,
i have a problem with nested results. i have to show the main categories and the total number of sub categories next to it. This number should include children for the children also. Say for example if i have a main category called music, and it has rock as a sub which may have soft rock and hard rock as its subs, then i have to show Music as Music(3) like in yahoo groups. i need a sql query to return me the values in this format. I am using SQL server 2000 so CTE is not possible. The table format is Category ID int ,CategoryName nvarchar and ParentCategoryI D int.
So any category that is added has a parentCategoryI D which will be a previously existing category. So the ParentCategoryI D contains a value of a previously existing CategoryID. also i could be having any number of main categories ie. its dynamic.
Could you please help me formulate a query for this?
i have a problem with nested results. i have to show the main categories and the total number of sub categories next to it. This number should include children for the children also. Say for example if i have a main category called music, and it has rock as a sub which may have soft rock and hard rock as its subs, then i have to show Music as Music(3) like in yahoo groups. i need a sql query to return me the values in this format. I am using SQL server 2000 so CTE is not possible. The table format is Category ID int ,CategoryName nvarchar and ParentCategoryI D int.
So any category that is added has a parentCategoryI D which will be a previously existing category. So the ParentCategoryI D contains a value of a previously existing CategoryID. also i could be having any number of main categories ie. its dynamic.
Could you please help me formulate a query for this?