Обсуждение: SELECT only those values of table B which have a value in table A
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. What happens now is that, if my table "d" has for example 2 countries with values and 2 countries without values, but table d_nom has for all4 countries values, my end result would be only 50% of the real value, which is wrong. I have to multiply and to divide through the same amount of countries.
Could anyone give me a hint how this would work? Do I need to add a subquery?
Thanks a lot!
Stef
Вложения
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.
SELECTCOALESCE(r.name, '''') AS name,d.year_start AS year,SUM(d.value * d_nom.value) / SUM(d_nom.value) AS valueFROMpop_total_access_water AS dRIGHT JOINcountries_view AS c ON c.id = d.id_countryRIGHT JOINpop_total AS d_nom ON d_nom.id_country = d.id_countryRIGHT JOINsubregions_view AS r ON r.id = c.sreg_idWHERE(d.year_start = 1990 ) AND(d_nom.year_start = 1990 ) ANDd_nom.value <> 0GROUP BYr.name, d.year_startORDER BY 1,2Now, I would like to use from table "d_nom" only these countrieswhich 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