sql - Joining/Querying Self-Referential "Grandchildren" Tables

Question

Say I have a table as following:

countries

| ID    | Name    | Population | Continent |

Countries is self-referential, and has an association table:

alliances

| country_id | ally_id    |

I understand that I need to use the 'AS' keyword to join the table, say, as c1, c2, etc. But I can't quite wrap my head around how to go about this for grandchildren, great grandchildren, etc.

How would I write SQL, for example, to get the countries where they have an ally' who's ally's population is greater than 50 000 000?

I'm generating this SQL based off of models defined in code, so need to be able to support this kind of behaviour up to a user-defined depth.

Thanks!

标签： sqlpostgresql

Answer 1

您的示例的查询是

SELECT c1.name
FROM countries AS c1
   JOIN alliances AS a ON c1.id = a.country_id
   JOIN countries AS c2 ON a.ally_id = c2.id
WHERE c2.population > 50000000;

如果这是一个家庭作业问题（看起来像一个），那么您已经找到了为您做这件事的傻瓜。