Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Location
    Finland
    Posts
    2

    Unanswered: Problem: "joining" data from two tables

    Hi,

    I'm a quite newbie with sql. I have problem with joining data from two tables. I think that example will describe it in the best way.
    HTML Code:
    Table 1:
    Date            Data1
    12.12.2000       3
    13.12.2000       4
    15.12.2000       6
    
    Table 2:
    Date             Data2
    12.12.2000       4
    14.12.2000       8
    15.12.2000       9
    16.12.2000       4
    
    Result (this is a thing that i need)
    Date            Data1      Data2
    12.12.2000       3             4
    13.12.2000       4            (0)
    14.12.2000       (0)           8
    15.12.2000       6             9
    16.12.2000       0             4
    Numbers in () are values that does not really exist in tables 1 or 2, but they are understood as zero. If I use
    Code:
    select table1.date, data1, data2 from table1,table2 where table1.date=table2.date
    result does not contain rows (dates) that where _not_ in both tables.

    -Timo

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you want is a FULL OUTER JOIN:
    Code:
    select coalesce(Table1.Date,Table2.Date) as Date
         , coalesce(Table1.Data1,0) as Data1
         , coalesce(Table2.Data2,0) as Data2
      from Table1
    full outer
      join Table2
        on Table1.Date = Table2.Date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Location
    Finland
    Posts
    2

    Thanks

    Thanks, it works now.

Posting Permissions

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