SQL: How To Create Two Fields Based On Same Field From Another Table?

I am sure this is quite easy to find, but through my searching I have been unable to find a solution. I'm probably not looking for the right keywords, so hopefully someone can help

Solution 1:

You just need to add another JOIN:

SELECT     ...various other fields...
           ACL.[CountryName] AS AgencyCountryName, 
           CCL.[CountryName] AS ClientCountryName
FROM       BookingData BD
INNER JOIN CountryList ACL ON BD.[Agency Country Code] = ACL.[CountryCode]
INNER JOIN CountryList CCL ON BD.[Client Country Code] = CCL.[CountryCode]