From: | Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> |
---|---|
To: | Michal Politowski <mpol+pg(at)meep(dot)pl> |
Cc: | 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 09:28:42 |
Message-ID: | 32422B19-575B-4C87-8951-CB4B42663345@grid.unep.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> 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.
Thanks for that. Ok, tried that, didn't work. But gave me the go into
the right direction. Stripped down the query to the absolute
necessary, on a country basis, and realized that I have in some tables
NULL values. So, the correct SQL is then (with the added line:
(d.value IS NOT NULL )):
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.value IS NOT NULL ) AND
(d_nom.year_start = 1990 ) AND
d_nom.value <> 0
GROUP BY
r.name, d.year_start
ORDER BY 1,2
Thanks a lot!
Stef
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-02-25 09:32:27 | Re: Global Temp Table |
Previous Message | Shameem Ahamed | 2010-02-25 09:03:50 | Global Temp Table |