MERGE TWO TABLES INTO SINGLE HIERARCHY??

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?? Received on Fri Dec 11 1998 - 15:37:50 CST