Re: Difficult query (for me)

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)