How to combine tables

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 Received on Wed Nov 11 1998 - 00:00:00 CST