From: Lee Harr <missive@frontiernet.net>
To: pgsql-sql@postgresql.org
Subject: Re: Difficult query (for me)
Date: Fri, 4 Apr 2003 22:58:16 +0000 (UTC)
Message-ID: <b6l2m8$lvs$1@news.hub.org> (raw)
References: <4ff986f9.0304041206.1ae7b97a@posting.google.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: missive@frontiernet.net
Subject: Re: Difficult query (for me)
In-Reply-To: <b6l2m8$lvs$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox