Are you looking for something like this?

DECLARE @AdjacencyList TABLE

(

Parent VARCHAR(20)

,Child VARCHAR(20)

);

DECLARE @PersonsLocations TABLE

(

Person VARCHAR(20)

,Location VARCHAR(20)

);

INSERT INTO @AdjacencyList

SELECT 'North America','USA'

UNION ALL SELECT 'USA','Georgia'

UNION ALL SELECT 'Georgia','Fulton County'

UNION ALL SELECT 'Georgia','Dekalb County'

UNION ALL SELECT 'Fulton County','Atlanta'

UNION ALL SELECT 'Dekalb County','Decatur';

INSERT INTO @PersonsLocations

SELECT 'Joe','Atlanta'

UNION ALL SELECT 'Jane','Decatur';

WITH ExpandHierarchy AS (

SELECT n=1, Person, Location, Parent, Child

FROM @PersonsLocations a

INNER JOIN @AdjacencyList b ON b.Child = a.Location

UNION ALL

SELECT n+1, a.Person, a.Location, b.Parent, b.Child

FROM ExpandHierarchy a

INNER JOIN @AdjacencyList b ON a.Parent = b.Child

)

SELECT Person, Location

FROM (

SELECT n, Person, Location=Parent

FROM ExpandHierarchy

UNION ALL

SELECT 0, Person, Location

FROM @PersonsLocations) a

ORDER BY Person, n;