In article <4ff986f9.0304041206.1ae7b97a@posting.google.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 | 100b | 100c | 100d | 100e | 100f | 100
(6 rows)
test=# select * from two;n | v
---+-----a | 25b | 50c | 75g | 150h | 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 | 75b | 100 | 50 | 50c | 100 | 75 | 25d
| 100 | | 100e | 100 | | 100f | 100 | | 100g | | 150 | -150h |
| 150 | -150
(8 rows)
