Difficult Select!!!! - 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)

Difficult Select!!!!

Author

Topic

Anastasiosyal
Starting Member

8 Posts

Posted - 2001-08-03 : 19:47:40

A recursive or tree style table is a table which could have a Foreign key to itself. In my situation I have a product categories table. The one below


TB_Categories
----------------
catId INT IDENTITY
catParentId INT
catName nvarchar(50)

Let us suppose that it has the following data


TB_Categories
------------------------------------------------
catId    |catParentId              |catName
------------------------------------------------
1        |0                        |Root    1
2        |0                        |Root    2
3        |1                        |SubRoot 1.1
4        |2                        |SubRoot 2.1
5        |1                        |SubRoot 1.2
6        |0                        |Root    3
7        |2                        |SubRoot 2.2
8        |1                        |SubRoot 1.3
9        |3                        |SubRoot 1.1.1
10       |3                        |SubRoot 1.1.2
11       |6                        |SubRoot 3.1
12       |11                       |SubRoot 3.1.1
13       |11                       |SubRoot 3.1.2
------------------------------------------------

You can represent the data in the above table as a tree
I supppose you get the idea.

My problem is this...

Let's say i am in category
9 |3 |SubRoot 1.1.2
I want to build an sql statement which will return all the categories above it AND/OR
including the category i am in. i want a T-Sql which will return

------------------------------------------------
1 |0 |Root 1
3 |1 |SubRoot 1.1
9 |3 |SubRoot 1.1.2
------------------------------------------------

You may wonder why i need this...
i want to be able to say in the asp page

you are here:
Root 1>Subroot 1.1>Subroot1.1.2

Any suggestions????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources