CODEWITHSUNDEEP
mysqljoinsubquery
Rahul M
Rahul M

Reputation: 1509

Turning mysql subquery into Join

I'm new to mysql & just started learning it. Last night I was trying to re-form following sub-query on country table of world database, into a join.

SELECT continent, NAME, population FROM country c WHERE
population = (SELECT MAX(population) FROM country c2 
WHERE c.continent=c2.continent AND population > 0)

I tried following query and several others with inner join etc. but failed. I'm getting result with the following query where max population is as expected but continent & country name as different. 

SELECT c.continent, c2.name, MAX(c2.population) AS pop FROM country  c, country c2 
WHERE c.continent = c2.continent GROUP BY continent

Please help, how can I get same result as the sub-query above.

Thanks in advance

Upvotes: 0

Views: 308

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

You should get the MAX(population) with GROUP BY continent inside a subquery, then JOIN it with the table itself; Like this:

SELECT c1.continent, c1.NAME, c1.population 
FROM country c1 
INNER JOIN
(
   SELECT continent, MAX(population) AS Maxp
   FROM country
   WHERE population > 0
   GROUP BY continent
) AS c2  ON c1.population = c2.maxp 
        AND c1.continent  = c2.continent;

Upvotes: 1

Related Questions