Re: Efficient parent/child/ancestral relationship DB algorithms

From: David Field <davidf_at_rockdb.demon.co.uk>
Date: Mon, 28 Jun 1999 11:23:18 +0100
Message-ID: <930567034.20758.0.nnrp-10.9e981d27@news.demon.co.uk>

The system I am working on has alot of parent/child relationships, so I can sympathise with your problem. I couldn't quite follow your examples though, as your relationship doesn't seem to be a straight parent/child one, as you have a child with two parents from your example code ( child role ID 5 has parents of 2 and 3).

We always use the SQL CONNECT BY clause when trying to navigate relationships like this. I am guessing that you are not aware of the CONNECT clause, as it wasn't mentioned in your mail. I won't attempt to explain it here as it is well documented, but basically you join the table to itself using the appropriate columns as:

select cus_id

,              cus_id_parent
from        customer

connect by cus_id = cus_id_parent.

There is also a START WITH clause to specify the record to use for the top node, and a handy LEVEL variable which specifies at which level of the tree you are on. This is handy to use for padding spaces onto the fron of any descriptions returned so that the hierarchy can be displayed graphically. There is also a CONNECT BY PREVIOUS clause, but I forget its use now.

They are pretty useful for standard parent / child relationships, but I'm not sure how they will cope with your 'lattice' structure.

Hope this is helpfull,

Dave. Received on Mon Jun 28 1999 - 05:23:18 CDT