Yeah - I've been looking around and I think you're right. This is really quite astonishing - seems like a pretty fundamental concept...
Thanks anyway! --- Chris <[EMAIL PROTECTED]> wrote: > No similar operation in SQL unfortunately. Wish > there was though... > > Good Luck. > Chris > > ---------------------------------------------- > Original Message > From: "Richard Heiser"<[EMAIL PROTECTED]> > Subject: Re:RE: SQL syntax for Supertypes-Subtypes > AKA Circular Reference > AKA Entity Tree > Date: Wed, 27 Aug 2003 10:32:02 -0700 (PDT) > > >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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
