Re: Difficult query (for me)

In article <4ff986f9(dot)0304041206(dot)1ae7b97a(at)posting(dot)google(dot)com>, Stephane wrote:
> Hello,
>
> Basicaly I have two tables like this:
>
> Table 1
> RecordName Value
> a 100
> b 100
> c 100
> d 100
> e 100
> f 100
>
> Table 2
> RecordName Value
> a 25
> b 50
> c 75
> g 150
> h 150
>
> I would like a query that give me a result like this:
> a 75 (100-25)
> b 50 (100-50)
> c 25
> d 100
> e 100
> f 100
> g -150
> h -150
>
> It is table1-table2.
> I could do it for records a,b,c but I do not how to have record which
> are only in one of the two tables.
> Thanks for your help.
> Stephane.

test=# select * from one;
n | v
---+-----
a | 100
b | 100
c | 100
d | 100
e | 100
f | 100
(6 rows)

test=# select * from two;
n | v
---+-----
a | 25
b | 50
c | 75
g | 150
h | 150
(5 rows)

test=# select coalesce(one.n, two.n), one.v as v1, two.v as v2,
test-# coalesce(one.v, 0)-coalesce(two.v, 0) as difference
test-# from one full outer join two on (one.n = two.n);
case | v1 | v2 | difference
------+-----+-----+------------
a | 100 | 25 | 75
b | 100 | 50 | 50
c | 100 | 75 | 25
d | 100 | | 100
e | 100 | | 100
f | 100 | | 100
g | | 150 | -150
h | | 150 | -150
(8 rows)