Hi, What you are trying to do is a very common situation. The Child-Parent model is a workable solution.
You might really want to take a look at the Nested Set Model (or NTM, Nested Tree Model). The NTM model uses a different approach to establishing hierarchies. Check out Dave Medinets stored procedures which deals with the uglier aspects of trees for you! http://www.codebits.com/ntm/ Also see http://www.dbmsmag.com/9604d06.html The nested set model is an excellent way to deal with hierarchies. Hope this helps! Thanks Jeremy Allen elliptIQ Inc. -----Original Message----- From: Jason Blum [mailto:[EMAIL PROTECTED]] Sent: Monday, October 29, 2001 12:07 PM To: CF-Talk Subject: *** Complicated SQL riddle Howdy all! Here's a SQL problem that's been vexing me for a few weeks now: Say you have a table like this: ID Name Boss 1 John 1 2 Mary 2 3 Steve 1 4 Mike 2 5 Susan 3 6 Max 2 7 Michelle 6 So, John is his own boss, the big cheese, and everybody reports to him or to someone who reports to him, or to someone who reports to someone who reports to him, etc. That's the problem - I need to keep it flexible to accommodate any number of levels because I want to output a tree illustrating the hierarchy: -John ---Mary ------Mike ------Max ---------Michelle ---Steve ------Susan I think I've heard someone talking about "self-joins" but am not sure how they'd work and whether they'd be flexible enough - anyone have any ideas? -Jason ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
