On 23 February 2010 11:44, Stefan Schwarzer <stefan.schwarzer@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
