I asked a very similar question a few weeks ago. One solution was posted by Jochem van Dieten:
Use a nested set model, or use a 2 table model where one table holds complete trees in an XMLData field (which you only need to edit when a new element is inserted) and one holds individual messages but with an FK to the table that holds the complete trees in XML. I usually prefer the first solution, but in some cases the latter solution allows you to offload the whole thing to the client and let the client figure it out using XSLT. Something like: http://spike.oli.tudelft.nl/jochemd/test/tree/test.xml If you want more info, check out the archives. The thread was: Re: recursion in cold fusion ( was RE: Creating a list with infinite groupings and indents ) -----Original Message----- From: Blum, Jason (SAA) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 11:13 AM To: CF-Talk Subject: SQL syntax for Supertypes-Subtypes AKA Circular Reference AKA Entity Tree Hello, Here's a puzzle for all you 'Joe Celko' types: Am trying to figure out how to do on SQL Server something I can do in Oracle. I think this problem is variously known as Subtypes-Supertypes, Circular Reference, Entity Tree, etc... Given this data: Columns: iEntityID, iSubEntityOf, sName 1 1 Mary 2 2 1 Fritz 3 3 1 John 4 4 2 Abu 5 5 2 Ludwig 6 6 3 Abigail 7 7 3 Josef 8 8 6 Mark 9 9 6 Ben 10 10 6 Habib 11 11 9 Paul 12 12 11 Mahatma I want to graphically represent the tree in this data - i.e. Mary is the boss; Fritz and John report to Mary; Abu and Ludwig report to Fritz; etc... The important thing is that the SubEntityOf column is kind of a foreign key to the primary key EntityID, such that the tree can be infinitely deep. I used to be able to do this in Oracle using: <CFQUERY NAME="GetEntities" DATASOURCE="MyDatasource"> SELECT iEntityID, iSubEntityOf, sName FROM Mytable WHERE NOT iEntityID START WITH iEntityID=1 CONNECT BY PRIOR iEntityID=iSubEntityOf </CFQUERY> But I can't use these functions in SQL Server... Thanks! -Jason ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
