Search code examples
sqljoininner-join

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 me.

I have two tables and I am trying to run a select query that will create two new fields based on the same field from another table.

Example: Table1 contains two fields called AgencyCountryCode and ClientCountryCode so I need to create two new fields called AgencyCountryName and ClientCountryName. In Table2 I have a list of country codes and country names to match with.

I have used the below code to match the agency country code to the name, but I can't figure out how to do the same thing for client, using the same lookup table/field.

SELECT
...various other fields...
CountryList.[CountryName] AS AgencyCountryName, 
FROM BookingData 
INNER JOIN CountryList ON BookingData.[Agency Country Code] = CountryList.[CountryCode];

Can anyone please let me know how I can perform the same lookup on two different fields?

Many thanks in advance.

Solution

  • 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]