Re: Subqueries or Joins? Problems with multiple table query

In response to Stefan Schwarzer :
> Hi there,
>
> gush, shouldn't be that complicated. But neither in Postgres, nor in Access I
> succeed in getting the result I wish.
>
> I have a couple of times for the Environmental Conventions (Kyoto, Montreal,
> CITES etc.). They look like this:
>
> id_country,year,value
> 4,1992,0
> 4,1993,0
> 4,1994,0
> 4,1995,0
> 4,1996,1
> 4,1997,0
> 4,1998,0
> 4,1999,0
> 4,2000,0
>
> so that I can see the year where the treaty has been ratified by a country.
> (The rows with the zero values seems to be unnecessary, I have to check that
> again with the data supplier.)
>
> Now, I would like to have a list of all (european) countries and the treaties
> they have signed, in the following style:
>
> country_name,year_kyoto,signed_kyoto,year_montreal,signed_montreal,....
> Germany 1996 1 1992
> 1 ....
> France 1995 1 1994
> 1 ...
>
> Again, the field with the "signed_..." is not necessary, but I just want to be
> sure that the query is running correctly.
>
> I tried it with subqueries - something like this:

Not sure if i understand you corrently, if not, provide more information
(table structure and data), if possible copy&paste - able.

Okay, let me try:

test=*# select * from country ;
id | name
----+---------
1 | germany
2 | use
3 | france
(3 rows)

test=*# select * from conventions ;
id_country | convention | year
------------+------------+------
1 | Kyoto | 1996
1 | Montreal | 2002
2 | Kyoto | 1998
(3 rows)

test=*# select c.name, sum(case when c2.convention='Kyoto' then c2.year
else null end) as kyoto, sum(case when c2.convention='Montreal' then
c2.year else null end) as montreal from country c left join conventions
c2 on c.id=c2.id_country group by c.name;
name | kyoto | montreal
---------+-------+----------
germany | 1996 | 2002
use | 1998 |
france | |
(3 rows)

(i know, silly and wrong data, only for example)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99