It's hard to use the first approach with an unlimited number of levels. So, you could do it in CF (as was suggested previously in this thread). If you can avoid that I would.
Otherwise, go with one of the other methods ( Nested Sets or what I described ) At 12:23 PM 11/14/2006, you wrote: >The thing is my real data goes much deeper then 3 levels, sometimes up to 10 >to 15 levels, but there are no restrictions on the depth. This is why I was >thinking recursion was the answer. > >Duane > >-----Original Message----- >From: Jeffry Houser [mailto:[EMAIL PROTECTED] >Sent: Tuesday, November 14, 2006 12:49 PM >To: SQL >Subject: Re: Recursion in SQL ??? > > I believe this approach is known as an 'adjacency model'. > > In theory self-joining queries are recursive, so you could do >something like this: > >select * >from MyTable MyTable1 join MyTable MyTable2 on (MyTable2.ParentID = >MyTable1.ID) > > I'm not going to commit to this being perfect without testing >it. It will work great if you just want to go two levels deep. But, >your example shows three levels deep. You can add the table a 3rd >time w/ another alias. If you don't know how deep the tree is, this >gets more complex, though. > > If you have the ability to modify the database structure, you might >examine a post I made about such things, here: ><http://www.jeffryhouser.com/index.cfm/2006/3/31/Alternatives-to-Database-Re >cursion> >... This approach is ideal if you rarely need to change the order of >the tree (Such a discussion thread). > > Also check out the nested set model: ><http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427> >which is better when the tree does need a lot of updates. > >At 11:35 AM 11/14/2006, you wrote: > >Is it possible to create a recursive query in SQLServer? > > > >I have a table that contains a parent/child record relationship and I want > >to build a tree without client side processing. > > > >ID Parent > >1 Null > >2 1 > >3 Null > >4 2 > >5 1 > >6 Null > >7 2 > >8 6 > > > >Needs to be sorted as > >-1 > > - 2 > > - 4 > > - 7 > > - 5 > >-3 > >-6 > > - 8 > > > > > >TIA, > >Duane > > > >-- >Jeffry Houser, Software Developer, Writer, Songwriter, Recording Engineer >AIM: Reboog711 | Phone: 1-203-379-0773 >-- >My Company: <http://www.dot-com-it.com> >My Books: <http://www.instantcoldfusion.com> >My Recording Studio: <http://www.fcfstudios.com> >Connecticut Macromedia User Group: <http://www.ctmug.com> >Now Blogging at <http://www.jeffryhouser.com> > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2623 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
