From: | Michal Politowski <mpol+pg(at)meep(dot)pl> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT only those values of table B which have a value in table A |
Date: | 2010-02-25 08:48:16 |
Message-ID: | 20100225084816.GA2923@meep.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Shameem Ahamed | 2010-02-25 09:03:50 | Global Temp Table |
Previous Message | Wappler, Robert | 2010-02-25 08:47:20 | Re: helo |