Jason, You've got the right idea, but I would recommend you make John's boss be 0, since he would be the top of the chain. Then all you have to do is make your query and group by ID and Boss. You can easily show all the data using cfoutput or you can use cftree.
Richard ----- Original Message ----- From: "Jason Blum" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, October 29, 2001 9:07 AM 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 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
