you should have 3 tables an owner table (owner and ownerID) a sub object table (subobject and subobjectID) a relationship table (ownwerID and subobjectID are complex primary key )
On Mon, 15 Apr 2002, Paul Giesenhagen wrote: > 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 > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
