| 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 |
<<attachment: smime.p7s>>
