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