September 8, 2003 at 10:30 am
Hi all,
I need to record a tree in a database in SQL Server.
When i make a quiery i must find out all the parent nodes for the current node.
Eg:
1(parent)- 2,3 (children)
2(parent)- 4,5 (children)
When i make the quiery with the parameter 4 i must get 2 and 1.
The tree could be very big.
Please tell me how can i implement the database ?
Thanks
September 9, 2003 at 12:19 am
Leonx,
a cutdown version of what you might be looking for is:
create table TblTree (Node INT UNIQUE, ParentNode INT NULL)
GO
insert TblTree select 1, null
insert TblTree select 2, 1
insert TblTree select 3, 1
insert TblTree select 4, 2
insert TblTree select 5, 2
-- just for fun, populate with a lot more descendents
declare @i INT
set @i = 6
while @i < 1000
begin
insert TblTree select @i, @i - 1
set @i = @i + 1
end
A function to navigate up the tree, plus a sample "Ancestors" query on this structure might be:
CREATE FUNCTION MyParents
(@Node int)
RETURNS @TblParents TABLE
(Id INT IDENTITY, ParentNode INT UNIQUE)
AS
BEGIN
WHILE 1 = 1 BEGIN
SELECT TOP 1 @Node = ParentNode FROM TblTree WHERE Node = @Node
IF @Node IS NULL OR @@ROWCOUNT < 1 BREAK
INSERT @TblParents VALUES (@Node)
END
RETURN
END
GO
SELECT ParentNode FROM dbo.MyParents (99) ORDER BY ID
Cheers,
- Mark
Cheers,
- Mark
September 9, 2003 at 12:19 am
How up to date must your data be. Do you have control on that ?
If yes, you could have a simple normalized table structure and a "relation" table. after loading your data in those tables, you could explode it to whatever form you want it. This explosion (i.e. what your query could be) will have major impact on your server. Maybe, you could have prepared (exploded) data presented to your query and have it accessed only through key-values.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2003 at 2:32 am
Thanks for your answers.
I found a very good article about this subject on http://www.wintellect.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply