Re: Subqueries or Joins? Problems with multiple table query

On 23 February 2010 11:44, Stefan Schwarzer
<stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> wrote:
> 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:
> SELECT countries.name, (SELECT yearAS basel FROM basel WHERE value = 1 AND
> countries.id = basel.id_country) AS basel, (SELECT yearAS cites FROM cites
> WHERE value = 1 AND countries.id = cites.id_country) AS cites FROM
> countries, basel, cites
> (without the field "signed_..." then), but it seems not to be correct.
> I tried it as well with JOINs, but there, too, no success.
> Can anyone give me a hint?
> Thanks a lot,
> Stef

Is this what you're after?

Select countries.name, basel.year, basel.value, cites.year, cites.value
>From countries
Left Join basel on basel.id_country = countries.id_country and basel.value=1
Left Join cites on cites.id_country = countries.id_country and cites.value=1

Regards

Thom