SQLzoo, SELECT within SELECT tutorial

Question

I am currently doing this tutorial (http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial) and I can't answer question 8 :

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

.. and my current query won't be accepted as the answer :

SELECT x.name, x.continent FROM world x
WHERE (x.population * 3) > ALL (
SELECT y.population FROM world y
WHERE x.continent = y.continent )

What am I doing wrong ? What is the answer ?

Answer 1

The issue with your query is that you're not excluding the "bigger" country itself from the result in the inner query. The correct query is:

SELECT x.name, x.continent
FROM world x
WHERE x.population > ALL(
    SELECT (y.population*3)
    FROM world y
    WHERE x.continent=y.continent
    AND x.name<>y.name
)

Note the last condition in the inner query where I'm excluding the "x" country from the list of "y" countries by doing x.name<>y.name. If that is not done, no rows will be returned in the result.

P.S. Usually the "exclusion" of the outer entity from the list of entities in the inner query is excluded by using id field, but the table on sqlzoo does not have id fields.