Xref: alice comp.databases.oracle.misc:23720
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!newsfeed.slurp.net!not-for-mail
Message-ID: <3672D52E.6A745196@Feist.Com>
From: "KeyStroke (Jack L. Swayze Sr.)" <KeyStrk@Feist.Com>
Reply-To: KeyStrk@Feist.Com
X-Mailer: Mozilla 4.5 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: MERGE TWO TABLES INTO SINGLE HIERARCHY??
References: <74s3dn$e6a$1@plug.news.pipex.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 163
Date: Sat, 12 Dec 1998 14:42:23 -0600
X-Trace: newsfeed.slurp.net 913495664 198.247.8.76 (Sat, 12 Dec 1998 14:47:44 CDT)
NNTP-Posting-Date: Sat, 12 Dec 1998 14:47:44 CDT

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
 -- (phoney top node marker)
 );

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...
>
> I have four tables that I need to join up.
>
>     CompanyMaster  (CompanyUID, CompanyName)
>     CompanyHierarchy (CompanyUID,ParentCompanyUID)
>     ResourceMaster (ResourceUID,ResourceName)
>     ResourceAssignments(ResourceUID,CompanyUID)
>
> The 'CompanyMaster' table holds a list of all companies. The
> 'CompanyHierarchy' table is a self joining table that implements a
> multi-tier hierarchy of parent companies with multiple child companies. The
> 'ResourceMaster' table holds a list of employees. Each employee works for
> one company. The assignments are stored in the 'ResourceAssignments' table.
>
> What I'm trying to create is a single query result set that includes both
> company entries and resource entries in one self joining result set.
>
>     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
>
> What I'm after is a two column result set that integrates both the  resource
> assignments into the company hierarchy, like...
>
>     World
>         |----- Europe
>                     |------- UK
>                     |           |------- Fred
>                     |           |------- Joe
>                     |------- France
>                                 |------- Jacque
>                                 |------- Eric
>
> Because the CompanyUID and ResourceUIDs are unique, then my guess is that
> any result table would have to perfix some form of 'entity' name to front of
> the UID code, like...
>
>         ChildUID            ParentUID
>
>            RES1             COMPANY2
>            RES2            COMPANY2
>             RES3            COMPANY3
>             RES4            COMPANY3
>         COMPANY3     COMPANY1
>         COMPANY2       COMPANY1
>
> Any ideas??