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:2622 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
