Re: trees, WITH operator, and traversal

Nis Jorgensen <nis_at_dkik.dk> wrote in message news:<sr3m3uoj165fko30fsrb3u1msihsqhrit9_at_4ax.com>...
> On 28 Dec 2001 06:21:02 -0800, gnuoytr_at_rcn.com (robert) wrote:
>
> >referencing mr. celko's "sql for smarties" 2nd; #28.7.3, db2 WITH
> >operator and fig. 29.2, tree diagram. given data that successive
> >nodes are A, C, H (stored in a temporary table, the context is jdbc so
> >host variable manipulation is not facile); is there a sql statement
> >which returns K, L? all of the examples i've seen on the general
> >subject show how to return all the child nodes from a given node.
> >what i need is to traverse a tree through a given set of node
> >identifiers. the context is the (in)famous One True Lookup Table
> >problem.
>
> For those who do not have the book:
>
> A is the parent of C, C is the parent of H, H is the parent of K and L
>
> A and C have other children as well.
>
> It is not clear to me what your problem is. Could you perhaps state
> more precisely:
>
> 1) What exactly is known by the nodes in the temporary table?
> 2) What exactly characterises the nodes you want to select, in
> relation to the nodes in the temporary table?

rather than returning all nodes from the parent down, return one leaf node for a given set of superior nodes (in order from root to the leaf node's predecessor). in the data structure, all paths will terminate in one node, e.g. A - B - E or A - C - G not A - C - H - K|L. i had also posted the specific problem on the ibm-db2 forum because i'm working in db2. there appears to be a solution which uses the db2/sql99 specific WITH syntax, which implements recursion.

the specific problem is to provide context specific sub-lists from lists to populate html select lists. an example would be state names. in this case, to un-generalize, A == 'state', B == 'region', C == 'division', D == 'manager', H == 'plastics', K is not there, L == FK to a cross reference table. the cross reference table contains FKs to a list table. so, at runtime, we get 'state', 'division', 'plastics' from the client program. we have to return just those states which the user has defined to be allowable for the plastics division. there are different paths for region and manager for state (and different sub-lists), and a root tree for each input field in the system.

so, we have to walk down the tree to get the FK. my current structure is the 5NF as explained in mr. celko's book, but this structure doesn't lend itself to arbitrary "keys" and "values". the keys are semantically defined in the schema. thus a tree structure as a replacement.

what i am wondering is whether there is a general syntax for doing this in sql92.

toodles,
robert Received on Thu Jan 10 2002 - 19:07:35 CET