From: Lee Harr <missive@frontiernet.net>
Subject: Re: Difficult query (for me)
Date: Fri, 4 Apr 2003 22:58:16 +0000 (UTC)
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 78
Message-ID: <b6l2m8$lvs$1@news.hub.org>
References: <4ff986f9.0304041206.1ae7b97a@posting.google.com>
Reply-To: missive@hotmail.com
User-Agent: slrn/0.9.7.3 (FreeBSD)
To: pgsql-sql@postgresql.org

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 | 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)