Tree Questions - 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)

Tree Questions

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-04-28 : 11:07:50

Hello,
I am trying to implement the folder structure in my database. I am trying to implement robvolk's lineage idea on representing trees.

This will just keep track of folders.
This is the schema for the folder table
Folder (Folder_ID(PK),Folder_Name,Parent_Folder,lineage,depth)

So these are my questions...
1) How do I display the Folder Structure?
From robvolk's article I wrote a query like this..

select space(depth*2)+Folder_Name from Folders
order by Lineage + LTrim(Str(Folder_ID,6,0))

But the problem with this is always it will not return the accurate hierarchy structure.

2) How do I get the full path name for each folder?

Thanks
maximus_vj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-28 : 12:23:22

There are several articles on this site about hierachies. Here is how I usually do it:


set nocount on
declare @items		Table (itemid int identity(1,1), itemName varchar(10))
declare @association	Table (itemid int, parentitemid int)
declare @tree		Table (parentitemid int, itemid int, lev int, struct varchar(200))

declare @lev int
set @lev = 0

insert @items (itemname)
select 'item01' union
select 'item02' union
select 'item03' union
select 'item04' union
select 'item05' union
select 'item06' union
select 'item07' union
select 'item08' union
select 'item09' union
select 'item10' union
select 'item11' union
select 'item12'
order by 1

insert @association
select 1,null union 
select 2,1 union
select 3,1 union
select 4,3 union
select 5,4 union
select 6,2 union
select 7,2 union
select 8,6 union
select 9,6 union
select 10,2 union
select 11,10 union
select 12, 1

insert @tree
select null, a.itemid, @lev, itemname
from	@association a
JOIN	@items i ON i.itemid = a.itemid
where	parentitemid is NULL

while @@Rowcount > 0
Begin
	set @lev = @lev + 1

	insert @tree
	select t.itemid, a.itemid, @lev, isNull(t.struct+'.','') + itemname
	from	@association a
	JOIN	@items i 
			ON i.itemid = a.itemid
	JOIN	@tree t 
			ON t.itemid = a.parentitemid
			and t.lev = @lev - 1
	Left JOIN @tree excl
			ON excl.parentitemid = a.itemid
	where	excl.itemid is NULL
End

select * from @tree order by itemid

select	replicate(char(9), lev) + 
	 convert(varchar,i.itemid) + '-' + 
	 itemname
From	@tree t
JOIN	@items i ON t.itemid = i.itemid
Order by i.itemid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources