Relationship between two tables with an additional third table between

Question

I have the following diagram and data.

--drop table table_c;
--drop table table_b;
--drop table table_a;

create table table_a (
  id    number(3,0)  primary key,
  value varchar2(10)
);

create table table_b (
  id    number(3,0)  primary key,
  a_id  number(3,0)  not null,
  value varchar2(10),
  constraint b_fk1 foreign key (a_id) references table_a(id)
);

create table table_c (
  id    number(3,0)  primary key,
  a_id  number(3,0)  null,
  b_id  number(3,0)  null,
  value varchar2(10),
  constraint c_fk1 foreign key (a_id) references table_a(id),
  constraint c_fk2 foreign key (b_id) references table_b(id)
);

-- table a
insert into table_a (id, value) values (1, 'a');
insert into table_a (id, value) values (2, 'b');
-- table b
insert into table_b (id, a_id, value) values (1, 1, 'aa');
insert into table_b (id, a_id, value) values (2, 2, 'bb');
-- table c with ref to a
insert into table_c (id, a_id, value) values (1, 1, 'aaa');
insert into table_c (id, a_id, value) values (2, 2, 'bbb');
-- table c with ref to b
insert into table_c (id, b_id, value) values (3, 1, 'ccc');
insert into table_c (id, b_id, value) values (4, 2, 'ddd');
COMMIT;

Basically this is the relationship between table_aand table_cwith the route through table_b, unless we have a direct link from table_cto table_a.

Each item in table_cwill be filled with either a_id, or b_id. If we have a_id, we have not b_id. If we have b_id, we have not a_id. Both cannot be nullsimultaneously or not equal to zero at the same time.

Now I am invited to create a materialized view that shows the connection between table_aand table_c.

, table_c, a_id . !

--drop materialized view mv_d;
--drop materialized view log on table_c;
--drop materialized view log on table_b;
--drop materialized view log on table_a;

create materialized view log on table_a with rowid, sequence;
create materialized view log on table_b with rowid, sequence;
create materialized view log on table_c with rowid, sequence;

create materialized view mv_d
  refresh fast on commit
  enable query rewrite
  as
    select a.value as a_val,
           c.value as c_val,
           a.rowid as a_rowid,
           b.rowid as b_rowid,
           c.rowid as c_rowid
      from table_a a,
           table_b b,
           table_c c
     where (c.a_id is null and c.b_id = b.id and b.a_id = a.id)
        or (c.a_id is not null and c.a_id = a.id);

execute dbms_stats.gather_table_stats( user, 'mv_d' ) ;

mv , , . . , rowid , , , .

select * from mv_d;

-- note, the rowids are for information only, but are abbreviated to only show how they're different.
 a_val | c_val | a_rowid | b_rowid | c_rowid
-------+-------+---------+---------+---------
 a     | aaa   | GAAA    | WAAA    | mAAA
 a     | ccc   | GAAA    | WAAA    | mAAC
 a     | aaa   | GAAA    | WAAB    | mAAA
 b     | bbb   | GAAB    | WAAA    | mAAB
 b     | bbb   | GAAB    | WAAB    | mAAB
 b     | ddd   | GAAB    | WAAB    | mAAD

select * from mv_d ( rowid, ofc).

 a_val | c_val 
-------+-------
 a     | aaa   
 a     | ccc   
 b     | bbb   
 b     | ddd   

?

, 3 , 6 1 table_a, table_b, table_c. 10 . .

Answer 1

select      a.value as a_val
           ,c.value     as c_val
           ,a.rowid     as a_rowid
           ,b.rowid     as b_rowid
           ,c.rowid     as c_rowid

from                    table_a a

            join        (           table_c c

                        left join   table_b b

                        on          c.b_id  = b.id
                        )

            on           a.id = nvl (b.a_id,c.a_id) 

;

---

select      a.value as a_val
           ,c.value     as c_val
           ,a.rowid     as a_rowid
           ,b.rowid     as b_rowid
           ,c.rowid     as c_rowid

from        dmarkovitz.table_a a
           ,dmarkovitz.table_b b
           ,dmarkovitz.table_c c

where       c.b_id  = b.id (+)
        and a.id    = nvl (b.a_id,c.a_id) 
;