Category and Subcategory Problem

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

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.

|||Thanks that worked great, exactly what I needed.

categories...

Hi,

i'm devoloping a web application that contains categories for example

Category1

Category11

Category111

Category1111

.........

Category112

Category12

Category2

categories with categories with categories... someone can help me about designing an elegant table for this situation.

Thanks

Fdo.

ive successfully used a technique very much like this:http://www.developerfusion.co.uk/show/4633/2/

this articel also offer a somewhat different approach that i've not tried:http://www.sitepoint.com/article/hierarchical-data-database/2

|||

excelent.

thanks Mike

categories table

hi to all;

i want to make in my site the possibility for the user to make categories in the site

ex: he can add the category name"computer science" and then can add the category childs " data mining, software engineering,... "

so firstly i want to make a category table in my databasde to be able to save the category data

haw can i make this table? i mean what are the columns that should be in the table?what type of relationship should i make? and how to mahe it

sorry but i need any one to explain to me what should i do. and if anyone one know the solution please reply to me

thanks....

Selfreferencing tables ?

Code Snippet

DROPTABLE Categories

GO

CREATETABLE Categories

(

CategoryId INTNOTNULL,

ParentCategoryID INT,

DescriptionVARCHAR(4000)

)

GO

ALTERTABLE Categories

ADDCONSTRAINT PK_Categories PRIMARYKEY(CategoryId)

GO

ALTERTABLE Categories

ADDCONSTRAINT FK_Categories_Cetegories FOREIGNKEY(ParentCategoryId)

REFERENCES Categories(CategoryId)

GO

Jens K. Suessmeyer.

http://www.sqlserver2008.de

Categories and Sub Categories

Hello,
I am working on a web site that makes documents available to students.
I am using tags to classify each document content.
So I have the following tables:
Documents, Tags, DocumentsTags
I also need to categorize the documents:
Subject
|-- Level
|-- Year
For example:
Document_1 - Math > University Level > Second Year
How can I create a table to create such classification?
Thank You,
MiguelHere is one way.
Have a table called Subjects, and add SubjectID to the Documents table with
a foreign key to Subjects (so that you only store "Math" once and it is easy
to update).
Add a column called Level to the Documents table.
Add a column called Year to the Documents table.
"shapper" <mdmoura@.gmail.com> wrote in message
news:24124f92-40c9-411e-8221-17119c78c7da@.j22g2000hsf.googlegroups.com...
> Hello,
> I am working on a web site that makes documents available to students.
> I am using tags to classify each document content.
> So I have the following tables:
> Documents, Tags, DocumentsTags
> I also need to categorize the documents:
> Subject
> |-- Level
> |-- Year
> For example:
> Document_1 - Math > University Level > Second Year
> How can I create a table to create such classification?
> Thank You,
> Miguel|||On Apr 24, 1:31 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Here is one way.
> Have a table called Subjects, and add SubjectID to the Documents table with
> a foreign key to Subjects (so that you only store "Math" once and it is easy
> to update).
> Add a column called Level to the Documents table.
> Add a column called Year to the Documents table.
> "shapper" <mdmo...@.gmail.com> wrote in message
> news:24124f92-40c9-411e-8221-17119c78c7da@.j22g2000hsf.googlegroups.com...
> > Hello,
> > I am working on a web site that makes documents available to students.
> > I am using tags to classify each document content.
> > So I have the following tables:
> > Documents, Tags, DocumentsTags
> > I also need to categorize the documents:
> > Subject
> > |-- Level
> > |-- Year
> > For example:
> > Document_1 - Math > University Level > Second Year
> > How can I create a table to create such classification?
> > Thank You,
> > Miguel
In the future I might need other categories. I was thinking in
something more flexible then that. Something like:
http://www.sqllessons.com/categories.html
I have two other tables:
Documents > DocumentID, Title, Description
DocumentsCategories > DocumentID, CategoryID
Then I was using Adjacency Model to create the table Categories.
This way I could have a Document associated to one or more categories
and at the same time I could have various levels of categories.
I am using LINQ to SQL so I would like to find a way to make this work
also with LINQ.
I am not sure if this is the best way but after Goggling this is the
closer I could find to solve my problem.
Thanks,
Miguel