August 3, 2001 at 4:32 pm
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????
--The greatest power is knowledge--
--The greatest power is knowledge--
August 3, 2001 at 7:31 pm
If you KNOW the max depth of the tree, you can just do a select that has a series of self joins (left outer from top to bottom). If you don't know the max depth, then I think you'd have to traverse the tree and either build the final select for dynamic execution (using Exec()) or just insert matching rows into a working table.
Andy
August 3, 2001 at 11:33 pm
Books Online does the second method that Andy speaks of (hierarchical information under the Index tab), by building a temporary table and interating up the relationship chain. I've done that, too, using a stored procedure that executes recursively (I was bored).
K. Brian Kelley
K. Brian Kelley
@kbriankelley
August 4, 2001 at 2:02 am
Andy,
Can you please give an example of the select you are talking about if a i have lets say...
4 levels of categories???
Thanx for your help folks!
It is very much appreciated!!!
--The greatest power is knowledge--
Edited by - Anastasiosyal on 08/04/2001 02:03:30 AM
--The greatest power is knowledge--
August 4, 2001 at 6:55 am
Havnt tested, but this shoudl be close - it should return the top level record where catid=1 and up to 3 levels of child records.
select L1.*, L4.* from tb_Categories L1 left join tb_categories L2 on L1.catid=L2.catparentid left join tb_categories L3 on L2.catid=L3.catparentid left join tb_categories L4 on L3.CatID=L4.catparentid where L1.catid=1
Andy
December 8, 2002 at 5:27 pm
We have a couple of ways of dealing with this. You have to accept that this is "denormalized" data and provide a way to update it when your org heirarchy changes.
First, we assign the "root" node a node ID of "AA". Then we select all its immediate children and assign them node IDs of their parent + "AA", "AB", "AC" etc in order. This allow each node to have 26826 children. After then 2nd generation is completed, we assign all their children the same way. When you get done, you have a long varchar() field that looks like this
AAABABBABDAA.
You can see any node's children using SQL that is LIKE NODEID + '%'.
Again, the trick is that this informatin is dynamic and can change when you insert values into your hierarchy. It only works well when your hierarchy is fairly static.
We created a UDF that assigns the generations NODEID based on where this node is based on how many siblings it has. The "oldest" node is AA, 2nd oldest=AB, etc. all the way through ZZ. You can go to 3 characters per generation and add more characters (0-9, etc.) if 26*26 siblings exceeds your needs.
December 8, 2002 at 6:33 pm
Nice problem!
Needs to check maxlevels(endless loop)!
Set NoCount on
-- Drop Table TestII
GO
Create Table TestII(catId INT IDENTITY,catParentId INT,catName nvarchar(50))
go
insert TestII Values (0,'Root1')
insert TestII Values (0,'Root2')
insert TestII Values (1,'SubRoot1.1')
insert TestII Values (2,'SubRoot2.1')
insert TestII Values (1,'SubRoot1.2')
insert TestII Values (0,'Root3')
insert TestII Values (2,'SubRoot2.2')
insert TestII Values (1,'SubRoot1.3')
insert TestII Values (3,'SubRoot1.1.1')
insert TestII Values (3,'SubRoot1.1.2')
insert TestII Values (6,'SubRoot3.1')
insert TestII Values (11,'SubRoot3.1.1')
insert TestII Values (11,'SubRoot3.1.2')
insert TestII Values (11,'SubRoot3.1.3')
insert TestII Values (11,'SubRoot3.1.4')
insert TestII Values (13,'SubRoot3.1.3.1')
insert TestII Values (13,'SubRoot3.1.3.2')
insert TestII Values (16,'SubRoot3.1.3.1.1')
GO
Select * from TestII
GO
-- Drop Table TestIII
GO
Create Table TestIII(catId INT ,catParentId INT,catName nvarchar(500),aLevel tinyint)
GO
Insert TestIII
Select *,Cast(0 as TinyInt) as aLevel from TestII Where CatParentId=0
GO
Declare @i TinyInt,
@l Tinyint
Set @i=1
Set @l=0
While @i>0
Begin
Insert TestIII
Select l.catid,l.catparentId,ll.CatName+'->'+l.catName,ll.aLevel+1
From TestII as l
Inner join TestIII as ll
On ll.aLevel=@l And
ll.CatID=l.catParentId
Select @i=@@Rowcount,
@l=@l+1
End
GO
Set NoCount off
Select * from TestIII
catId catParentId catName aLevel
----------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
1 0 Root1 0
2 0 Root2 0
6 0 Root3 0
5 1 Root1->SubRoot1.2 1
3 1 Root1->SubRoot1.1 1
8 1 Root1->SubRoot1.3 1
4 2 Root2->SubRoot2.1 1
7 2 Root2->SubRoot2.2 1
11 6 Root3->SubRoot3.1 1
9 3 Root1->SubRoot1.1->SubRoot1.1.1 2
10 3 Root1->SubRoot1.1->SubRoot1.1.2 2
12 11 Root3->SubRoot3.1->SubRoot3.1.1 2
13 11 Root3->SubRoot3.1->SubRoot3.1.2 2
14 11 Root3->SubRoot3.1->SubRoot3.1.3 2
15 11 Root3->SubRoot3.1->SubRoot3.1.4 2
16 13 Root3->SubRoot3.1->SubRoot3.1.2->SubRoot3.1.3.1 3
17 13 Root3->SubRoot3.1->SubRoot3.1.2->SubRoot3.1.3.2 3
18 16 Root3->SubRoot3.1->SubRoot3.1.2->SubRoot3.1.3.1->SubRoot3.1.3.1.1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply