Getting Parent of Parent in Self Join Table

Question

I have self join table. This table is being used to join up to 4 level, i.e.;

Region -> Country -> County -> Town

How can I get Parent of Parent of Town. To do this up to two level this is the query

SELECT t.ShortName AS Town,
       (SELECT c.ShortName FROM Locations c 
               WHERE c.LocationId = t.ParentId) AS County
FROM Locations t
WHERE t.LocationId = 100

Now want to get Country which is parent of County.

Answer 1

Either hardcode another join or use a recursive CTE.

;with locs as
(
select 1 as level, ShortName, ParentId
from Locations 
WHERE LocationId = 100
UNION ALL
SELECT level + 1, l.ShortName, l.ParentId
FROM Locations l 
JOIN locs ON locs.ParentId = l.LocationId
)
SELECT * FROM locs;

Answer 2

Just pretend its 4 separate tables, using nicely named aliases:

SELECT town.ShortName as TownName,
       county.ShortName as CountyName,
       country.ShortName as CountryName,
       region.ShortName as RegionName
FROM Locations town
INNER JOIN Locations county  ON town.ParentID = county.LocationID
INNER JOIN Locations country ON county.ParentID = country.LocationID
INNER JOIN Locations region  ON country.ParentID = region.LocationID
WHERE town.LocationID = 100

If not every town has a county, country, and region, then some of those might need to be LEFT OUTER joins.