Re: How to combine tables

From: Dave Wotton <Dave.Wotton_at_no-spam.it.camcnty.gov.uk>
Date: 12 Nov 1998 09:17:24 GMT
Message-ID: <72e934$sgr$1@dns.camcnty.gov.uk>

DanHW <danhw_at_aol.com> wrote:

>>I'd like to know how to combine tables. I need to create a denormalized
>>table to import to a mainframe app. This is not the same as merging
>>tables, where each row of the destination table corresponds to a row in
>>the source tables.
>> [snip]
>
>Views should work fine. My guess with your out joins not working is that you
>are doing a 3-way table join, and losing the rows on the 3rd join. If you do an
>outer join, all the columns in the 'extra' row are null. If you are then trying
>to use that as a join column to another table, it won't find a match, unless
>you play some games with the NVL function...
>
>select a.id, a.descr, b.id, b.descr from a,b where
>a.id = b.id(+)
>
>will return a row for every row in A. For those rows in A with no match in B,
>the B.id and b.descr columns will be null. This you know.
>
>If you now want B.id2 to reference something in C...
>
>select a.id, a.descr, b.id, b.descr, b.id2, c.id2, c.descr from
>a,b,c
>where
>a.id = b.id(+) and
>nvl(b.id2, -1)=c.id2(+)
>
>(the -1 in the nvl function is any key NOT in table C)
>
>This will return all rows in A. For any record in B not in A, you will a null
>for B.id, and B.descr thanks to the outer join on table B. Now also note the
>join condition on the join with table C. If there is a value, it reduces to
>b.id2=c.id2 as desired. If B.ID2 is null ( the 'extra' rows), the nvl function
>returns a -1. By design, this returns a value NOT in table C, so the outer join
>on it makes the extra row in table C. Now all rows show up correctly.
>

Hi,

you don't need to play these sorts of games with nvl to make the outer join to the third table work. It works anyway. The outer join on the third table will return a row even if the join column from table2 is null ( because it too came from an outer join ).

create table dave1 ( id number , data varchar2(5) );
create table dave2 ( id number , data varchar2(5) );
create table dave3 ( id number , data varchar2(5) );

insert into dave1 values ( 1 , 'AAAA' );

insert into dave2 values ( 2 , 'BBBB' ); insert into dave3 values ( 3 , 'CCCC' );

select dave1.data, dave2.data, dave3.data   from dave1, dave2, dave3
 where dave1.id = dave2.id(+)
   and dave2.id = dave3.id(+);

DATA DATA DATA
----- ----- -----

AAAA                            <== Note: row returned ok.

To answer the original question you can, of course, use a view like the above, or if you really are trying to create a merged table ( perhaps for a data warehouse application ), you can then use the view to create a table containing the composite rows ...

CREATE TABLE XXXX AS
   SELECT DAVE1.DATA, DAVE2.DATA, DAVE3.DATA      FROM DAVE1, DAVE2, DAVE3
    WHERE DAVE1.ID = DAVE2.ID(+)
      AND DAVE2.ID = DAVE3.ID(+); Dave.
--
Remove the no-spam bit from my email address to reply. Received on Thu Nov 12 1998 - 03:17:24 CST