SQL Server does not have anything built in functions for trees. A nicer way of representing trees in a DB is to use the nested set model:
http://www.intelligententerprise.com/001020/celko.shtml Celko's book "SQL for Smarties" has a more thorough treatise on the issue. ----- Original Message ----- From: "Blum, Jason (SAA)" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, August 27, 2003 9:12 AM 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
