Problem: "joining" data from two tables

Timppa · #1 (**permalink**) 09-24-05, 06:44

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

r937 · #2 (**permalink**) 09-24-05, 07:43

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

Timppa · #3 (**permalink**) 09-24-05, 08:41

Thanks, it works now.

Internet Services

Web Development

Digital Marketing

Consumer Tech