Have you checked to see if DB/2 supports recursive queries? Most DBs don't, but some do. That'd be the best bet. Otherwise you're stuck with doing the recursion manual: run that query, then run it again for each returned row, repeating until there aren't any more rows.
If you have a common need to extract all decendants of a node and your tree is pretty static, the nested set model might be a good fit. It's somewhat more complex, but not terribly so, and is very good at extracting subtrees of arbitrary size in a single query. cheers, barneyb On 9/1/06, loathe <[EMAIL PROTECTED]> wrote: > I have an organization table. There is an org_id and a parent_org_id. > > I am trying to get all organizations from a certain point down. The > environment is DB/2. > > So far I'm doing: > > Select > org.org_id, > org.parent_org_id > From > organizations org > Left join > Organizations org2 > On > Org.org_id = org2.parentID > > Where org.org_id = #session.filter.org_id# > > > I know I have seen something similar to this before. It's no problem going > down one level but there seems to be a need to do some sort of recursion or > something in order to go lower than that. > > I'm beating myself over the head with this one, on a Friday afternoon, any > pointers would be much appreciated. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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/CF-Talk/message.cfm/messageid:251805 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
