Category and Subcategory Problem

Question

Hi everyone, I am having trouble with a particular problem with SQL. I have a table that defines product categories like so:

Id (int)
Text varchar(50)
ParentId (int)

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

9
13
15

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:

CREATE TABLE #Categories (
CategoryId int)

insert #Categories (CategoryId)
select Id
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,

Dylan

Answer 1

Please take a look at the link below:

 

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

 

You can use recursive CTEs in SQL Server 2005 to write the queries. You can encapsulate those in views or inline TVFs.

Answer 2

Thanks that worked great, exactly what I needed.