Selecting From Tree/recursive style Table

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

Transact-SQL (2000)

Selecting From Tree/recursive style Table

Author

Topic

Anastasiosyal
Starting Member

8 Posts

Posted - 2001-08-03 : 16:22:37

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