CODEWITHSUNDEEP
mysql
RD Ward
RD Ward

Reputation: 6737

MySQL: Select multiple values from a table based on multiple values from another table

I would like to return all the reports that are about each of these regions, that belong to a macroregion...

I would like to somehow 

SELECT DISTINCT report FROM reports WHERE region = 
(SELECT distinct region from macroregions where macroregion = 'Africa')

The regions in the macroregion are Sahara, West Africa, Tropical Africa,... etc

Although this is impossible since it the subquery would return multiple results.

Upvotes: 1

Views: 626

Answers (2)

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

This should get you what you need:

SELECT 
    r.report
FROM
    reports r
INNER JOIN
    macroregions m ON 
        m.region = r.region
        AND 
        m.macroregion = 'Africa'

This is all of the reports associated to regions associated to the macroregion 'Africa'.

Upvotes: 2

dynamic
dynamic

Reputation: 48121

SELECT DISTINCT report FROM reports WHERE 
region IN 
(SELECT distinct region from macroregions where macroregion = 'Africa')

Maybe you missed IN operator

Upvotes: 2

Related Questions