Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: MERGE TWO TABLES INTO SINGLE HIERARCHY??
create table CompanyMaster
(CompanyUID number unique, CompanyName varchar2(10));
create table CompanyHierarchy
(CompanyUID number unique, ParentCompanyUID number);
create table ResourceMaster
(ResourceUID number unique, ResourceName varchar2(10));
create table ResourceAssignments
(ResourceUID number unique, CompanyUID number);
comment on table CompanyMaster is
'Holds a list of all companies';
comment on table CompanyHierarchy is
'A self joining table that implements a multi-tier hierarchy of parent companies
with multiple child companies';
comment on table ResourceMaster is
'Holds a list of employees. Each employee works for one company';
comment on table ResourceAssignments is
'Stores assignments of resources to companies';
insert into CompanyMaster select * from (
select 1, 'Europe' from dual union select 2, 'UK' from dual union select 3, 'France' from dual union select 4, 'USA' from dual union select 5, 'West Coast' from dual union select 6, 'East Coast' from dual union select 7, 'World' from dual); insert into CompanyHierarchy select * from ( select 1, 7 from dual union select 2, 1 from dual union select 3, 1 from dual union select 4, 7 from dual union select 5, 4 from dual union select 6, 4 from dual union select 7, -1 from dual
insert into ResourceMaster select * from (
select 1, 'Fred' from dual union select 2, 'Joe' from dual union select 3, 'Jacque' from dual union select 4, 'Eric' from dual);
insert into ResourceAssignments select * from (
select 1 , 2 from dual union select 2 , 2 from dual union select 3 , 3 from dual union select 4 , 3 from dual);
SELECT CHILDUID, PARENTUID FROM
(SELECT 'COMPANY' || TO_CHAR(COMPANYUID) CHILDUID, 'COMPANY' ||
PARENTCOMPANYUID PARENTUID
FROM CompanyHierarchy
START WITH PARENTCOMPANYUID = -1
CONNECT BY PRIOR COMPANYUID = PARENTCOMPANYUID
UNION
SELECT 'RES' || RESOURCEUID CHILDUID, 'COMPANY' || COMPANYUID PARENTUID
FROM ResourceAssignments
)
WHERE PARENTUID != 'COMPANY-1'
;
CHILDUID PARENTUID
--------------- --------------
COMPANY2 COMPANY1 COMPANY3 COMPANY1 RES1 COMPANY2 RES2 COMPANY2 RES3 COMPANY3 RES4 COMPANY3 COMPANY5 COMPANY4 COMPANY6 COMPANY4 COMPANY1 COMPANY7 COMPANY4 COMPANY7
10 rows selected.
davout wrote:
> Can anybody help me create a SQL statement to solve the following problem...
>
>
> CompanyMaster (CompanyUID, CompanyName)
> CompanyHierarchy (CompanyUID,ParentCompanyUID)
> ResourceMaster (ResourceUID,ResourceName)
> ResourceAssignments(ResourceUID,CompanyUID)
>
>
>
> CompanyMaster
> CompanyUID CompanyName
> 1 Europe
> 2 UK
> 3 France
> 4 USA
> 5 West Coast
> 6 East Coast
> 7 World
>
> CompanyHierarchy
> CompanyUID ParentCompanyUID
> 1 7
> 2 1
> 3 1
> 4 7
> 5 4
> 6 4
> 7 -1 (phoney top node marker)
>
> ResourceMaster
> ResourceUID ResourceName
> 1 Fred
> 2 Joe
> 3 Jacque
> 4 Eric
>
> ResourceAssignments
> ResourceUID CompanyUID
> 1 2
> 2 2
> 3 3
> 4 3
>
>
> World
> |----- Europe
> |------- UK
> | |------- Fred
> | |------- Joe
> |------- France
> |------- Jacque
> |------- Eric
>
>
> ChildUID ParentUID
>
> RES1 COMPANY2
> RES2 COMPANY2
> RES3 COMPANY3
> RES4 COMPANY3
> COMPANY3 COMPANY1
> COMPANY2 COMPANY1
>