[GENERAL] 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.




                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





Thanks a lot!

Stef

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: 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?

smime.p7s
Description: S/MIME cryptographic signature