merge data help

[gvp16]

Hi, Ive got 3 tables in a database

 

barcode,  products,  prices  the 3 tables link the data via a product code, so each table has a "productCode" feild.

 

Im looking to put them into a single table containing the contents and fieids from all the tables.

 

BUT!  there is data in some tables that isnt in other ones that I would like to save, so for example, there may be a barcode and product, but no price. or a product and no barcode or price.

 

Anyone know how i can do this?

 

Thanks.

[Adam]

You can join the barcodes and prices tables using left joins:

 

select pro.productCode
     , pro.anotherColumn
     -- any other columns from products
     , pri.priceColumn
     -- any other columns from prices
     , bar.barcodeColumn
     -- any other columns from barcodes
from products pro
left join prices pri on (pro.productCode = pri.productCode)
left join barcodes bar on (pro.productCode = bar.productCode);

 

Obviously you'll need to modify it a little, but once done run and make sure it returns the data you want. I weren't sure if by merge you meant permanently within a new table, or for within a select statement; so if you want to physically join them then you can modify the query to create a table using the select statement:

 

create table new_products
    select pro.productCode
         , pro.anotherColumn
         -- any other columns from products
         , pri.priceColumn
         -- any other columns from prices
         , bar.barcodeColumn
         -- any other columns from barcodes
    from products pro
    left join prices pri on (pro.productCode = pri.productCode)
    left join barcodes bar on (pro.productCode = bar.productCode);