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
