Parent and Child Hierarchy Query on same table? 1

[wellster34]

Oracle 8i

Hi,

I have an issue on how to extract data from a single table that stores the data for different levels of a Hierarchy in the same columns. Here's an example:


PARENT_VALUE CHILD_VALUE
100 200
100 300
200 ABC
200 DEF
300 ZZZ

So, if I query 100 for a Parent. I return 200 and 300. Then I need to query the same table for 200 and 300. So on and so on until I get to the bottom of the hierarchy. The hierarchy is like this to be extracted:

100 200 ABC
100 200 DEF
100 300 ZZZ

Is there an Oracle function/query to extract this information or is it a coding logic nightmare?

Thanks for your time

 

[johnherman]

check out CONNECT BY

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[SantaMufasa]

Wellster,

John points to the correct solution in Oracle. But since the "CONNECT BY" (+"START WITH") syntax and logic is so esoteric, I'll post a proof of concept of a solution to your need:

create table wellster (parent_value varchar2(5), child_value varchar2(5));
insert into wellster values (null,'100');
insert into wellster values ('100','200');
insert into wellster values ('100','300');
insert into wellster values ('200','ABC');
insert into wellster values ('200','DEF');
insert into wellster values ('300','ZZZ');

col hierarchy format a12
select lpad(' ',level * 3) || child_value Hierarchy
  from wellster
connect by prior child_value = parent_value
start with parent_value is null;

HIERARCHY
------------
   100
      200
         ABC
         DEF
      300
         ZZZ

Let us know your thoughts about this remarkable Oracle syntax.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[johnherman]

I absolutely love it. No such syntax with Transact SQL or DB2 SQL.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[SantaMufasa]

BTW, John, notice that I added in a record at the "top" of the hierarchy for "Child 100" to have a "Parent" of NULL. This shows how to deal with the "Big Dog" in a hierarchy and to start the hierarchy from the top, if necessary.

You can, however, show just a portion of the hierarchy, beginning at whatever record you choose, by simply changing the "START WITH" condition to look for some specific situation that does not involve the top of the hierarchy.

Also, you can to a bottom-up listing (versus the top-down portrayal, above), by simply flipping the "CONNECT BY" condition to read:

connect by prior parent_value = child_value

And you are correct: I have not seen any other DBMS vendor produce this compact, tree-walking syntax. (...but I know others are trying, but generally with these results: .)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

At which point, they sniff and mumble something about "not ANSI SQL!".

 

[wellster34]

Awesome! Thanks for your help... You saved me lots of coding.