Обсуждение: SELECT only those values of table B which have a value in table A

Поиск
Список
Период
Сортировка

SELECT only those values of table B which have a value in table A

От
Stefan Schwarzer
Дата:
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
Вложения

Re: SELECT only those values of table B which have a value in table A

От
Michal Politowski
Дата:
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.

Re: SELECT only those values of table B which have a value in table A

От
Stefan Schwarzer
Дата:
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
Вложения