Re: Hierarchical query

> Say if there is an extra field for the referential key, how do you do
> the query?

If you have the table:

create table test (code number primary key,   description varchar2(30),
  category number references test);

with the following data (note the order of insertion, it does metter):

insert into test1 values(4, 'Salary', null);
insert into test1 values(1,'E1', 4);
insert into test1 values(2,'E2', 4);
insert into test1 values(3,'E3', 4);
insert into test1 values(9,'Benefit', null);
insert into test1 values(5,'E4', 9);
insert into test1 values(6,'E5', 9);
insert into test1 values(7,'E6', 9);
insert into test1 values(8,'E7', 9);

You may use the following query:

SQL> select a.description, b.description   2 from test1 a, test1 b
  3 where b.code = a.category
  4 /

DESCRIPTION                    DESCRIPTION
------------------------------ ------------------------------
E1                             Salary
E2                             Salary
E3                             Salary
E4                             Benefit
E5                             Benefit
E6                             Benefit
E7                             Benefit

Nothing really Oracle-specific here. Simple self-join.

V.K. Received on Mon Mar 21 2005 - 02:26:26 CST