sql question - 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 - 15:50:36 CET