On Thu, 25 Feb 2010 09:34:19 +0100, Stefan Schwarzer wrote: > Hi there, > > I have tables with values for each country of the world. > > Now, there is the possibility for the user to generate regional > (Europe, Africa...) or subregional (Western Europe, Central > Europe...) aggregations on-the-fly. In some cases, these > aggregations need to be calculated by using the population of each > region too, as a denominator, which looks like this: > > > SELECT > COALESCE(r.name, '''') AS name, > d.year_start AS year, > SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value > FROM > pop_total_access_water AS d > RIGHT JOIN > countries_view AS c ON c.id = d.id_country > RIGHT JOIN > pop_total AS d_nom ON d_nom.id_country = d.id_country > RIGHT JOIN > subregions_view AS r ON r.id = c.sreg_id > WHERE > (d.year_start = 1990 ) AND > (d_nom.year_start = 1990 ) AND > d_nom.value <> 0 > GROUP BY > r.name, d.year_start > ORDER BY 1,2 > > Now, I would like to use from table "d_nom" only these countries > which have a value in table "d" as well. Without thinking much (so this may be completely wrong for some reason/additional changes may be needed), why do you use a right join to the d_nom table? An ordinary inner join should give you only these countries which have their rows in the d table. -- Michał Politowski Talking has been known to lead to communication if practiced carelessly. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
