Basic SQL Technique

I've never liked SQL too much and I've never been particularly skilled in it, so probably what I'm going to show in this post is pretty obvious, but anyway. This is something that I used like 12 years ago and when last week I faced the same issue it took me some head scratching to come up with the solution, so maybe in other 12 years I'll find useful having done a DumpToBlog here. The technique is using the same table twice in the same query.

Let's say I have 2 tables like this:

And we went to obtain a listing like this:

So we have 2 different columns in the Countries table that point to Cities table. The solution is pretty simple, just use the Cities table in our query twice, using a different alias. I mean:

select countries.Name, C1.Name as Capital, C2.Name as MostPopulated
from countries, cities C1, cities C2
where countries.CapitalCityId = C1.Id
and countries.MostPopulatedCityId=C2.Id 

Hum, probably my shortest post so far.