view on multiple tables

Question

I am bit stucked with my Lab from Object-Relational dtb. I have this hierarchy:

SUPERTYPE EMPLOYEE_TYPE3
------------------------------
empno number(4)
ename varchar(10),
job varchar(9),
hiredate date,
sal number(7, 2),
deptno number(2)


SUBTYPE MANAGER_TYPE3
------------------------------
office varchar(6),
car varchar(10)


SUBTYPE SALESMAN_TYPE3
------------------------------
comm number(7, 2)


SUBTYPE STAFF_TYPE3
------------------------------
office varchar(6)

And I made table for every subtype under the employee_type3. What I need to do is to create a view ALL_EMPLOYEES that includes data from all employee subtypes. The view should contain all the columns from each subtype. It is obvious that in some columns will be null values.

Does anyone know how to do such view? I think that union is useless here because I have different number of columns.

Thank you very much for your help!

Answer 1

You indeed use a union (but a UNION ALL).

You null-extend all rows so they have the same structure:

select Col1, NULL as Col2, 1 as Discriminator FROM T1 union all
select NULL as Col1, Col2, 2 as Discriminator FROM T2

Note, that I included a discriminator so you can filter on the type (or even determine it).

Answer 2

I found the solution, so if anyone is interested in similar issue, you can find here some inspiration. Solution:

create view all_employees 
(
 empno,
 ename,
 job,
 hiredate,
 sal,
 comm,
 deptno,
 office,
 car
) 
as 
select empno,
       ename,
       job,
       hiredate,
       sal,
       null,
       deptno,
       office,
       car 
  from manager 
union 
select empno,
       ename,
       job,
       hiredate,
       sal,
       comm,
       deptno,
       null,
       null 
  from salesman 
union 
select empno,
       ename,
       job,
       hiredate,
       sal,
       null,
       deptno,
       office,
       null 
  from staff

There is only need to add null, where is not defined column for subtype.