select B1.Branch_Name, B2.Branch_Name FROM Branches B1 LEFT JOIN Branches B2 ON B1.branch_id = B2.sub_branch
Might have to play with the join a little bit, but that's the basic concept. This only does branches and sub-branches. It gets significantly more complex when your sub branches have their own subs, and sub's subs have subs, etc. At that point, depending on the size of the data, it's often much easier to just load it all into a structure and process in CF (faster too). Sam At 12:57 PM 12/19/2002, you wrote: >Almost got a self join working the way I want, but not quite... > >I've got a table of Office Branches. Some of them are MAIN BRANCHES (value >"main" under "sub_branch" in table). >Others are SUB BRANCHES and would have the branch_ID of the main branch >listed in the "sub_branch" cell. > >So, I need a self join to get this to work, correct? Can't get the syntax >just right.... > >Table: BRANCHES >--------------- >ID >branch_name >branch_ID >sub_branch > > >Return results like >------------------ > >Main Branch 1 > SubBranch 1 > SubBranch 2 > >Main Branch 2 > >Main Branch 3 > SubBranch 3 > >Some assistance please? > >Thanks.... > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
