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

An ordinary inner join should give you only these countries which have
their rows in the d table.

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?

                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

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

smime.p7s
Description: S/MIME cryptographic signature