sql - Create a view to merge all data from 2 tables -

sql - Create a view to merge all data from 2 tables -

tables , b have n columns same name (common columns), each table has own special columns - table has x additional columns , table b has y additional columns. column1* - db-wide unique id, rows in tables , b cannot merged. need create viewab include info in tables , b.

we tried union (but puts info different columns single column if not name columns), total outer bring together did not help because in view see mutual columns appear twice (i.e. column1 , column1_1 etc). ideal solution create query in not need name mutual columns (there lot), fine if have them named.

see image how see our info (http://imgur.com/u2zt98g).

please, find below scripts set tables , b.

--tablea create table tablea ( common1 varchar2(50), common2 varchar2(50), common3 varchar2(50), a1 varchar2(50), a2 varchar2(50), a3 varchar2(50), constraint common1_pk primary key (common1) ); insert tablea (common1, common2, common3, a1, a2, a3) values ( '1c1', '1c2', '1c3', '1a1', '1a2', '1a3'); insert tablea (common1, common2, common3, a1, a2, a3) values ( '2c1', '2c2', '2c3', '2a1', '2a2', '2a3'); insert tablea (common1, common2, common3, a1, a2, a3) values ( '3c1', '3c2', '3c3', '3a1', '3a2', '3a3'); --tableb create table tableb ( common1 varchar2(50), common2 varchar2(50), common3 varchar2(50), b1 varchar2(50), b2 varchar2(50), b3 varchar2(50), b4 varchar2(50), constraint common1b_pk primary key (common1) ); insert tableb (common1, common2, common3, b1, b2, b3, b4) values ( '1c1b', '1c2b', '1c3b', '1b1', '1b2', '1b3', '1b4'); insert tableb (common1, common2, common3, b1, b2, b3, b4) values ( '2c1b', '2c2b', '2c3b', '2b1', '2b2', '2b3', '2b4'); insert tableb (common1, common2, common3, b1, b2, b3, b4) values ( '3c1b', '3c2b', '3c3b', '3b1', '3b2', '3b3', '3b4'); insert tableb (common1, common2, common3, b1, b2, b3, b4) values ( '4c1b', '4c2b', '4c3b', '4b1', '4b2', '4b3', '4b4'); --viewab --( -- common1 varchar2(50), -- common2 varchar2(50), -- common3 varchar2(50), -- a1 varchar2(50), -- a2 varchar2(50), -- a3 varchar2(50), -- b1 varchar2(50), -- b2 varchar2(50), -- b3 varchar2(50), -- b4 varchar2(50) --)

you need specify columns (you can't utilize select * you've found), , union all info 2 tables. might missing include placeholders columns other table in each part of union. example:

select common1, common2, common3, a1, a2, a3, null b1, null b2, null b3, null b4 tablea union select common1, common2, common3, null a1, null a2, null a3, b1, b2, b3, b4 tableb

because have null values no info type, may need cast the, varchar2, @ to the lowest degree in first part, e.g.

cast (null varchar2(50)) b1, ...

sql fiddle demo.

sql view oracle11g merge outer-join