I've seen numerous threads throughout this list discussing the need for trees stored in sql server.
I came across this absolutely fantastic article at http://www.sqlteam.com/item.asp?ItemID=8866, where the author compares the two models for building trees: Adjacency List Model and Nested Set Model. He then outlines his method for using Adjacency Lists. I won't get into nested set model, but a table for the adjacency list model looks like so: EmployeeID FullName ParentID 1 SomePerson <null> 2 SomeLackey 1 3 AnotherLackey 1 4 Peon 2 Although I agree that it's better normalized to use Nested Set Model, the queries to perform actions are obscenely huge. The author on this link has written all of those queries required for Nested Set Model: http://www.codebits.com/ntm/ None theless, I've built a stored procedure some of you guys might find useful when using the adjacency list model. I've based it on Rob Volk's model. I just tweaked it to work with most of my tables. It returns fields similar to the tables outlined in Volk's idea. I added an isParent field to help with displaying in cold fusion. If anyone wishes to see this in action on my web server, go to http://voyager.voyus.com/test.cfm Feel free to butcher it to your heart's content: CREATE PROCEDURE dbo.sys_treeView --Created By Costas Piliotis @TableName as varchar(1000), --Name of adjascency list table @idField as varchar(1000), --Primary Key for said table @parentField as varchar(1000), --Parent ID Field for said table @valueField as varchar(1000) --Value from that table to display in tree AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @strSQL as nvarchar(4000) create table #tblTemp ( ident int not null, fieldvalue nvarchar(1000) not null, parent int, depth int, lineage nvarchar (1000), isParent bit) set @strSQL = 'insert into #tblTemp (ident, fieldvalue, parent) select ' + @idField + ', ' + @valueField + ', ' + @parentField + ' from ' + @tableName + ' where ' + @parentField + ' is not null and ' + @idField + ' is not null and ' + @valueField + ' is not null' exec sp_executesql @strSQL set @strSQL = 'insert into #tbltemp (ident, fieldvalue) select ' + @tableName + '.' + @idField +', ' + @tableName +'.'+ @valueField + ' from '+ @tableName + ' inner join ' + @tableName +' dt on ' + @tableName + '.' + @idField + ' = dt.' + @parentField + ' where ' + @tableName + '.' + @parentField + ' is null group by ' + @tableName +'. ' + @idField + ', ' + @tableName + '.' + @valueField exec sp_executesql @strSQL UPDATE #tblTemp set parent = null where parent = ident UPDATE #tblTemp SET Lineage='/', Depth=0 WHERE parent Is NULL WHILE EXISTS (SELECT * FROM #tblTemp WHERE Depth Is Null) BEGIN UPDATE P SET isParent = 1 FROM #tblTemp AS T INNER JOIN #tblTemp AS P ON (T.Parent=P.ident) WHERE P.Depth>=0 AND P.Lineage Is Not Null AND T.Depth Is Null UPDATE T SET T.depth = P.Depth + 1, T.Lineage = P.Lineage + Ltrim(Str(T.Parent,6,0)) + '/' FROM #tblTemp AS T INNER JOIN #tblTemp AS P ON (T.Parent=P.ident) WHERE P.Depth>=0 AND P.Lineage Is Not Null AND T.Depth Is Null END UPDATE #tblTemp set isparent = 0 where isparent is null select * from #tblTemp order by lineage + Ltrim(str(ident,6,0)) drop table #tblTemp GO ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
