Um, I appreciate the response! But this is way over my head. Does SQL Server really not have a counterpart to Oracle's functions "START WITH" and "CONNECT BY PRIOR"???
Seemed like such a simply query in Oracle: ><CFQUERY NAME="GetEntities" > DATASOURCE="MyDatasource"> > > SELECT iEntityID, iSubEntityOf, sName > > FROM Mytable > > WHERE NOT iEntityID > > START WITH iEntityID=1 > > CONNECT BY PRIOR iEntityID=iSubEntityOf > ></CFQUERY> Thanks! -Richard --- Chris <[EMAIL PROTECTED]> wrote: > Here is a stored procedure I used for an old forum > app. Should work for > your situation. > > --DROP TABLE #stack > --DROP TABLE #x > --DROP TABLE #y > --CREATE PROCEDURE expand (@current int) as > DECLARE @current int > DECLARE @level int, @line char(20) > DECLARE @Pos int > DECLARE @PrevReplyTo int > CREATE TABLE #stack (item int, Thislevel int) > CREATE TABLE #x (ID int, Name varchar(256), > DateCreated datetime, InReplyTo > int, Topic int, TotalReply int, Creator int, IsTop > bit, Root int, TopDate > datetime, _USER_ID int, MessageFrom > varchar(256),ThisLevel int, Position > int) > CREATE TABLE #y (ID int, InReplyTo int, Root int) > SET @current = 94 > SET @Pos = 0 > SET @PrevReplyTo = @current > INSERT #y > SELECT ID,InReplyTo, Root > FROM Threads > WHERE Root = @current > ORDER BY DateCreated DESC > > INSERT #x > SELECT Threads.ID AS "ID", > Threads.Name AS "Name", > Threads.DateCreated AS "DateCreated", > Threads.InReplyTo AS "InReplyTo", > Threads.Topic AS "Topic", > Threads.TotalReply AS "TotalReply", > Threads.Creator AS "Creator", > Threads.IsTop AS "IsTop", > Threads.Root AS "Root", > Threads.TopDate AS "TopDate", > Users.ID AS "_USER_ID", > Users.Userid AS "MessageFrom", > 0, > 0 > FROM Threads,Users > WHERE Threads.Root = @current AND Threads.Creator = > Users.ID > > SET NOCOUNT ON > INSERT INTO #stack VALUES (@current, 1) > SELECT @level = 1 > > WHILE @level > 0 > BEGIN > IF EXISTS (SELECT * FROM #stack WHERE Thislevel = > @level) > BEGIN > -- If an item on the stack exists at this level, > grab it > SELECT @current = item FROM #stack WHERE Thislevel > = @level > > -- Increment the position > SET @Pos = @Pos + 1 > -- Update the table with the new position > UPDATE #x SET Position = @Pos, ThisLevel = @level > - 1 WHERE ID = @current > -- Delete this entry from the temp table (#y) > DELETE FROM #y WHERE ID = @current > > -- Delete this entry from the stack > DELETE FROM #stack WHERE Thislevel = @level AND > item = @current > -- Insert into the stack any IDs that are replies > to the CURRENT ID > INSERT #stack SELECT ID, @level + 1 FROM #y WHERE > InReplyTo = @current > -- If any were found, increment the level > > IF @@ROWCOUNT > 0 > SELECT @level = @level + 1 > END > ELSE > SELECT @level = @level - 1 > > > IF ((SELECT COUNT(*) FROM #y) > 0 AND (SELECT > COUNT(*) FROM #stack) = 0) > BEGIN > IF @level = 0 > SELECT @level = @level + 1 > > INSERT #stack > SELECT ID, @level + 1 FROM #y > SELECT * FROM #stack > END > > END -- WHILE > > DROP TABLE #stack > SELECT ID,Name,ThisLevel,Position FROM #x ORDER BY > Position,DateCreated > > > DROP TABLE #x > DROP TABLE #y > > > > > > ---------------------------------------------- > Original Message > From: "Hagan, Ryan Mr (Contractor > ACI)"<[EMAIL PROTECTED]> > Subject: RE: SQL syntax for Supertypes-Subtypes AKA > Circular Reference AKA > Entity Tree > Date: Wed, 27 Aug 2003 11:21:52 -0400 > > >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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
