How to create oracle view from different tables with no common values

Question

I have the following tables

COMPANY

 =========================
 |COMPANY_ID|COMPANY_NAME|
 =========================
 |      C1  |  Test1     | 
 |      C2  |  Test2     |
 =========================

DEPARTMENT

 ========================
 |DEPT_ID   | DEPT_NAME |  
 ========================
 |     D1   |  Sales    | 
 |     D2   |  HR       |
 ========================

COMPANY_DEPARTMENT_PROFILE

 ====================================================
 |PROFILE_ID| DEPT_ID   | COMPANY_ID | PROFILE_VALUE|
 ====================================================
 |       1  |  D1       |    C1      |  ACTIVE      |
 |       2  |  D2       |    C1      |  INACTIVE    |
 ====================================================

DEFAULT_PROFILE

 ========================================
 |DEFAULT_ID| DEPT_ID   |  PROFILE_VALUE|
 ========================================
 |       1  |  D1       |    ACTIVE     |
 |       2  |  D2       |    ACTIVE     |
 ========================================

The table logic is such that we maintain a table of available companies and departments which are not dependent on each other. Let's say all the companies in COMPANY table has all the departments in DEPARTMENT table.

COMPANY_DEPARTMENT_PROFILE has the company related, department profile information. This table may or may not have data for each company. In this example table, the COMPANY_DEPARTMENT_PROFILE has only entries for company C1 and not C2.

DEFAULT_PROFILE contains the default profile value for each department irrespective of company. This data can be overridden by value in COMPANY_DEPARTMENT_PROFILE for a specific company.

Now I need to create a view in the following format

=============================================
|COMPANY_ID|DEPT_ID|PROFILE_ID|PROFILE_VALUE|
=============================================
|    C1    |  D1   |    1     |   ACTIVE    |
|    C1    |  D2   |    2     |   INVACTIVE |
|    C2    |  D1   |    1     |   ACTIVE    |
|    C2    |  D2   |    2     |   ACTIVE    |
=============================================

The logic is I need to create a view with profile value for each department for each company. If the company has profile value already in the COMPANY_DEPARTMENT_PROFILE, we need to take values from there. But if the company does not have any entry in the COMPANY_DEPARTMENT_PROFILE then we need to populate the default values for that department from DEFAULT_PROFILE.

Answer 1

Isn't it what you're looking for?

SQL> with
  2    company as
  3      (select 'C1' company_id, 'Test 1' company_name from dual
  4       union all
  5       select 'C2', 'Test 2' from dual),
  6    department as
  7      (select 'D1' dept_id, 'Sales' dept_name from dual
  8       union all
  9       select 'D2', 'HR' from dual),
 10    company_department_profile as
 11      (select 1 profile_id, 'D1' dept_id, 'C1' company_id, 'ACTIVE' profile_value from dual
 12       union all
 13       select 2, 'D2', 'C1', 'INACTIVE' from dual),
 14    default_profile as
 15      (select 1 default_id, 'D1' dept_id, 'ACTIVE' profile_value from dual
 16       union all
 17       select 2, 'D2', 'ACTIVE' from dual)
 18  select
 19    c.company_id,
 20    d.dept_id,
 21    coalesce(cdp.profile_id, dp.default_id) profile_id,
 22    coalesce(cdp.profile_value, dp.profile_value) profile_value
 23  from
 24    company c
 25      cross join
 26    department d
 27      join
 28    default_profile dp
 29      on (dp.dept_id = d.dept_id)
 30      left outer join
 31    company_department_profile cdp
 32      on (cdp.company_id = c.company_id and cdp.dept_id = d.dept_id);

COMPANY_ID DEPT_ID  PROFILE_ID PROFILE_VALUE
---------- -------- ---------- --------------------------------
C1         D1                1 ACTIVE
C1         D2                2 INACTIVE
C2         D2                2 ACTIVE
C2         D1                1 ACTIVE

Answer 2

You can generate all the companies and departments using a cross join. Then bring in the additional profile information using left join and some logic to choose the default:

select c.company_id, d.dept_id,
       coalesce(cdp.profile_id, dp.profile_id) as profile_id,
       (case when cdp.profile_id is not null then cdp.profile_value else dp.profile_value end) as profile_value
from company c cross join
     department d left join
     company_department_profile cdp
     on cdp.company_id = c.company_id and cdp.dept_id = cdp.dept_id left join
     default_profile dp
     on d.dept_id = dp.dept_id;