Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unhappy Unanswered: Help joining tables

    I have 50 tables with identical structure but different data:
    --Table tbl1
    date StatusID
    1/1/03 21
    1/2/03 22
    1/3/03 45

    --Table tbl2
    date StatusID
    1/2/03 32
    1/10/03 21

    --Table tbl3
    date StatusID
    1/1/03 33
    1/2/03 67
    1/11/03 89

    ...

    I want to join the tables and show all dates and values (nulls for non-matching). This is where I am at with just 3 tables:
    SELECT A.timestamp, A.StatusID, B.StatusID, c.StatusID
    FROM _TBL1 as A
    FULL OUTER JOIN (
    SELECT timestamp, StatusID
    FROM _TBL2) as B
    ON a.timestamp = b.timestamp
    FULL OUTER JOIN (
    SELECT timestamp, StatusID
    FROM _TBL3) as C
    ON a.timestamp = c.timestamp
    order by A.timestamp

    Result set:
    date StatusID StatusID StatusID
    NULL NULL 21 NULL
    NULL NULL NULL 89
    1/1/03 21 NULL 33
    1/2/03 22 32 67
    1/3/03 45 NULL NULL

    What I would like for a result set:
    date StatusID StatusID StatusID
    1/1/03 21 NULL 33
    1/2/03 22 32 67
    1/3/03 45 NULL NULL
    1/10/03 NULL 21 NULL
    1/11/03 NULL NULL 89

    Any suggestions would be appreciated. Thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    select "timestamp"=coalesce(a."timestamp",b."timestamp",c ."timestamp"), a.statusid, b.statusid, c.statusid
    from "_tbl1" a
    full join "_tbl2" b on a."timestamp" =b."timestamp"
    full join "_tbl3" c on a."timestamp" =c."timestamp"
    order by "timestamp"

    I don´t recomend using a derived table as a replacement of an existing (real) table and datatype (timestamp) as column name.

    Coalesce function returns first not null parameter. See BOL.

    Enjoy your query
    Last edited by ispaleny; 01-16-03 at 11:11.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •