OK. I did not read your entire email. I assume you are trying to recursively lookup items. As someone said oracle CONNECT BY can do this otherwise you might need to to some nested queries.
On Mon, 15 Apr 2002, Paul Giesenhagen wrote: > Hmm.. here is what I have: > > department table > id | title | description ..... > (all departments go into here ..Fishing, Camping, Rods, Spinning ect..) > > And > > the table below from the original message > > Why would I use the sub object table .. So I would stick EVERY department > id in the OWNER table and EVERY department id in the SUB_OBJECT table.. and > then combine their ID's in the RELATIONSHIP TABLE? Isn't it already doing > something similar by sticking the ID value from the DEPARMENTS table in the > relationship table? > > I guess my question then would be why the owner and sub-object tables? It > seems a bit redundant? > > Paul Giesenhagen > QuillDesign > http://www.quilldesign.com > SiteDirector v2.0 - Commerce Builder > > > > > 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 > > > > > > > > > > > > > > > ______________________________________________________________________ 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
