I have two tables: ORGANIZATION and HIERARCHY.
- creating table organization and inserting values
create table ORGANIZATION( ID NUMBER not null, NAME VARCHAR2(10))
insert into ORGANIZATION (ID, NAME) values (1, 'A1');
insert into ORGANIZATION (ID, NAME) values (2, 'B1');
insert into ORGANIZATION (ID, NAME) values (3, 'B2');
insert into ORGANIZATION (ID, NAME) values (4, 'C1');
insert into ORGANIZATION (ID, NAME) values (5, 'C2');
commit;
--
- creating table hierarchy and inserting values
create table HIERARCHY( ORG_ID NUMBER not null, ORG_ID_PARENT
NUMBER not null)
insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (2, 1);
insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (3, 1);
insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (4, 2);
insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (5, 2);
commit;
--
The problem is how to get the result with two columns that would look like
this:
MASTER_ORG_ID, ORG_ID_CURRENT
A1 A1
A1 B1
A1 B2
A1 C1
A1 C2
B1 B1
B1 C1
B1 C2
B2 B2
C1 C1
C2 C2
I tried with CONNECT BY clause but I couldn't get the desired result.
Is it possible to write an SQL query returning the result described above?
Thanks
Received on Tue Mar 25 2003 - 08:50:36 CST
