Thursday, January 11, 2007 10:27 PM
Hi everyone, I am having trouble with a particular problem with SQL. I have a table that defines product categories like so:
It holds all our categories, with subcategories having the appropriate ParentId relating to the above category. I am trying to write a stored procedure that takes in a single Id, and finds out all the related subcategories and subcategories all the way down the tree. I need to produce a resultset with a single column of Id's of all the subcategories etc
For example if the table had the following records:
8 - General - 1
9 - Academic - 1
10 - Science - 1
11 - History - 8
12 - Maths - 8
13 - English - 9
14 - Spanish - 9
15 - England - 13
So if I was to feed in Id 9 the resulting table that I want is like this
My problem is that there isn't a defined number of subcategory levels. General has only 1 subcategory level, but Academic has 2 subcategory levels.
The only part solution I have found was this:
CREATETABLE #Categories (
CategoryId int) insert #Categories (CategoryId)
from Category as c1
where c1.ParentId=8 or c1.Id=8
however it only brings back the first level of subcategories. I was intending to loop this over and over however because of the inconsistent number of levels I can't put in a predefined number of loops.
I have never really faced a problem like this before. I am pretty new to T-SQL and am not sure if there is an easy way to overcome this, but any help would be very much appreciated. I was pretty much ready to pull out my hair yesterday trying to solve this .
I am using SQL Server Express 2005 on Windows Server 2003
Thanks in advance for any help,
Thursday, January 11, 2007 10:37 PMPlease take a look at the link below:You can use recursive CTEs in SQL Server 2005 to write the queries. You can encapsulate those in views or inline TVFs.
Friday, January 12, 2007 2:29 AMThanks that worked great, exactly what I needed.