Thread: Join required??

testdb=>
testdb=> select * from test1;

c|i
-+-
A|2
B|3
C|4
(3 rows)

testdb=> select * from test2;

c|i
-+-
A|6
C|7
(2 rows)

testdb=>

I'd like to get (test1.i + test2.i) like this:

c|sum
-+---
A|8
B|3
C|11


i.e. to sum the rows but if one of the rows doesn't exist on one of the tables
then print just the existing row. How can I do this with postgresql-6.5.3?


Thanks in advance,
Timo T.

On 18 Mar 2001 13:43:06 +0200, Timo Tuomi wrote:

> i.e. to sum the rows but if one of the rows doesn't exist on one of the tables
> then print just the existing row. How can I do this with postgresql-6.5.3?


Sounds to me like a GROUPed LEFT OUTER JOIN.
but you should give us more Information.

BTW: isn't postgresql-6.5.3 a bit too old? left outer join isn't
available before 7.1.

Dear Timo

At present I am using 7.0.2 and eagerly awaiting 7.1. Following qurey gives the
answer you want. It also covers case where table test2 has some rows that have
"i" not in table test1.  I am sure this will work on 6.5 too.


SELECT test1.c as c, (test1.i+test2.i) as i
from test1,test2
WHERE test1.c=test2.c
UNION
SELECT test1.c as name, test1.i as i
from test1
WHERE (not exists (select * from test2 where test1.c=test2.c) )
UNION
SELECT test2.c as name, test2.i as i
from test1
WHERE (not exists (select * from test1 where test1.c=test2.c));

Timo Tuomi wrote:

> testdb=>
> testdb=> select * from test1;
>
> c|i
> -+-
> A|2
> B|3
> C|4
> (3 rows)
>
> testdb=> select * from test2;
>
> c|i
> -+-
> A|6
> C|7
> (2 rows)
>
> testdb=>
>
> I'd like to get (test1.i + test2.i) like this:
>
> c|sum
> -+---
> A|8
> B|3
> C|11
>
> i.e. to sum the rows but if one of the rows doesn't exist on one of the tables
> then print just the existing row. How can I do this with postgresql-6.5.3?
>
> Thanks in advance,
> Timo T.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl