Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Merge two tables

Discussion in 'Information Technology' started by Francophone, Jul 28, 2006.

Tags:
  1. Francophone

    Francophone
    Expand Collapse
    Guest

    I have 2 tables with the following fields:
    Table1: customerID, shipdate, shipqty
    data is ([1,5/5/05,50],[1,6/5/05,100])
    Table2: customerID, returndate, returnqty
    data is ([1,5/14/05,2],[1,6/5/05,3])

    How can I merge this information into 1 table with the 5 different headings.
    My new table should look like this.

    CustomerID shipdate shipqty returndate returnqty
    1 5/5/05 50 - -
    1 - - 5/14/05 2
    1 6/5/05 100 6/5/05 3

    Note that if there isn't anything returned or shipped on a particular date,
    that field should be null or zero. Queries I've tried will put values into
    every field. For instance the data above with a select query would end up
    being 4 records as follows:

    CustomerID shipdate shipqty returndate returnqty
    1 5/5/05 50 5/14/05 2
    1 5/5/05 50 6/5/05 3
    1 6/5/05 100 5/14/05 2
    1 6/5/05 100 6/5/05 3

    Please help.
    TIA
     
  2. Loading...


  3. Linc

    Linc
    Expand Collapse
    Guest

    Hi Francophone,

    Try using the UNION ALL statement in SQL. Something like...

    SELECT * FROM Table1 UNION ALL SELECT * FROM Table2

    Chris


    "Francophone" wrote:

    > I have 2 tables with the following fields:
    > Table1: customerID, shipdate, shipqty
    > data is ([1,5/5/05,50],[1,6/5/05,100])
    > Table2: customerID, returndate, returnqty
    > data is ([1,5/14/05,2],[1,6/5/05,3])
    >
    > How can I merge this information into 1 table with the 5 different headings.
    > My new table should look like this.
    >
    > CustomerID shipdate shipqty returndate returnqty
    > 1 5/5/05 50 - -
    > 1 - - 5/14/05 2
    > 1 6/5/05 100 6/5/05 3
    >
    > Note that if there isn't anything returned or shipped on a particular date,
    > that field should be null or zero. Queries I've tried will put values into
    > every field. For instance the data above with a select query would end up
    > being 4 records as follows:
    >
    > CustomerID shipdate shipqty returndate returnqty
    > 1 5/5/05 50 5/14/05 2
    > 1 5/5/05 50 6/5/05 3
    > 1 6/5/05 100 5/14/05 2
    > 1 6/5/05 100 6/5/05 3
    >
    > Please help.
    > TIA
    >
     

Share This Page