Categories and SubCategories - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Categories and SubCategories

Author

Topic

iamnavid
Starting Member

6 Posts

Posted - 2006-06-06 : 04:05:08

Hi,

I've a table like this.
id,title,subid

and some records like,

1,title1,0
2,title2,1
3,title3,1
4,title4,2

Subid is a foreignkey to the same table ID column.

I want to create something like this

title1 > title2 > title4

where my categoryid is 4.

I need rows like this
1,title1
2,title2
4,title4

How could I do this? I need subcategories of title4. and subcategories of subcategorie of title 4 and ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-06 : 04:09:22

Refer this
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

iamnavid
Starting Member

6 Posts

Posted - 2006-06-06 : 04:19:23

But i need an SQL string to return just these rows, and nothing else,
I need the reverse job that this do.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 04:21:34

you need to provide the tables involved and their relationship to one another for us to help you build the tsql for that

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-06 : 04:26:10

Did you try that approach?
Or refer this http://sqlteam.com/forums/topic.asp?TOPIC_ID=67059

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-06 : 04:30:45

Is there a reasonable, and finite, limit to the number of levels you need to refer back to?

When we make breadcrumbs like this we just self-join the table to itself to get the parent, grand-parent ... data. We have a maximum of about 5 levels and it seems to work fine.

Kristen

iamnavid
Starting Member

6 Posts

Posted - 2006-06-06 : 04:34:42

there is no minimum or maximum for these levels, may be just one level or even 10-20 levels, but i think it would not increase more than 3 or 4.

Kristen
Test

22859 Posts

Posted - 2006-06-06 : 04:45:50

Store an additional "path" column that you can query to get all ancestors in one "hit"?

So your data would be:

1,title1,0,NULL
2,title2,1,0001
3,title3,1,0001
4,title4,2,00010002

(Decision to be made whether you store "self"'s ID in the Path, or not. We don't to save some storage)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources