Re: How to combine tables

From: DanHW <danhw_at_aol.com>
Date: 1998/11/12
Message-ID: <19981111232227.14592.00000419@ng63.aol.com>#1/1

>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.
>
>For example, I have two tables:
>
>PRODUCT (prod_no*, prod_desc, model_no)
>STOCK (prod_no*, qty_on_hand)
>
>I would like to create another table (PRODUCT_STOCK) where each row is
>the result of concatenating matching rows in either table. If a row
>exists in table PRODUCT and not in table STOCK, it should also appear in
>table PRODUCT_STOCK and vice versa
>
>The table PRODUCT_STOCK would look like:
>
>PRODUCT_STOCK (prod_no*, prod_desc, model_no, qty_on_hand)
>
>In effect, what I am trying to do is produce a denormalized table from
>normalized ones.
>
>In addition, the number of source tables is not limited to two, although
>five seems like the upper limit.
>
>I've tried a few ideas already. A view works partly, but fails when a
>row exists in one table but not the other (even with an outer join).
>Seems that I may have to resort to PL/SQL to solve this issue.
>
>Any help would be greatly appreciated.
>
>Patrick
>

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.

Dan Hekimian-Williams Received on Thu Nov 12 1998 - 00:00:00 CST