sql question - hierarchy

From: gringo <bond_at_james.bond>
Date: Tue, 25 Mar 2003 12:59:03 +0100
Message-ID: <b5pg27$5s3$1_at_sunce.iskon.hr>

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

[Quoted] [Quoted] I tried with CONNECT BY clause but I couldn't get the desired result. [Quoted] Is it possible to write an SQL query returning the result described above? Thanks Received on Tue Mar 25 2003 - 12:59:03 CET