If you're using Oracle, look up the "CONNECT BY" SQL+ feature. You can easily draw out trees of parent/child categories. http://www.orafaq.com/faqsql.htm#TREE
If not, look into sql self joins. That's a good way of denormalizing parent/child data in the same table. In fact, here's a good link for that. http://www.onlamp.com/pub/a/onlamp/2001/07/12/aboutSQL.html Good luck! EC -----Original Message----- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 10:08 AM To: CF-Talk Subject: Listing out departments (again) I have to ask this one again .. I asked this question late Friday and didn't get any answers that actually fit my situation. I will try to have this question make sense. I am trying to build an OUTPUT that would look like this: Fishing Fishing/Rods/Baitcast Fishing/Rods/Baitcast/Special Bait Cast Rods/... Fishing/Rods/Baitcast/Not so Special Rods/... Fishing/Rods/Spinning Fishing/Reels/BaitCast Fishing/Reels/Spinning Fishing/Line Camping Camping/Tents Camping/Stoves/Big Stoves Camping/Stoves/Little Stoves Camping/Sleeping Bags Marine/ ect... Ok as you can see, we have 3 main departments (actually more, but for example we will go with whats above), and multiple departments (unlimited depth) that are below each department. My table structure is this: ID = Identifying id OWNER = Parent SUB_OBJECT = Child (Example: Owner=FISHING and SUB_OBJECT=RODS ..then OWNER=RODS and SUB_OBJECT=BAITCAST) As you can see, parents can be children and children can become parents. I am inserting NAMES instead of ID values that are in the actual table (makes it more readable for this message). Each Owner/Sub_object is an ID Value that relates to another table called departments. As you can see, sub_objects end up becoming OWNERs with sub_objects below them. So parents can be children and children can become parents. Table Setup ID | OWNER | SUB_OBJECT 1 Index Fishing 2 Index Camping 3 Index Marine 4 Fishing Rods 5 Fishing Reels 6 Fishing Line 7 Rods Baitcast 8 Baitcast Special Baitcast Rods 9 Biatcast Not so Special Rods 10 Rods Spinning 11 Reels Baitcast 12 Reels Spinning 13 Camping Tents 14 Camping Stoves 15 Camping Sleeping Bags 16 Stoves Big Stoves 17 Stoves Little Stoves 18........ I know the INDEX value and each of my Index Sub_objects are my main catagories, and want wind down through all the departments and build a recursive list that starts with main catagory and build a "catagory/sub_dept/sub_dept/sub_dept ect... I know this is alittle loong and I appologize .. but I am dumbfounded and keep "looping" myself into nowhere, trying to get the output as it is above. Anyhelp with the SQL and CF Syntax is greatly appreciated! At this point, I am not looking for performance :) just a solution! Comon' I know that there is a supermind that can help me!! Thank you Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector v2.0 - Commerce Builder ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
