How to join tables in SQL by ID in another table?

Question

Suppose I have the following tables:

countryregion

CountryRegionCode | Name                 
------------------------------------------
AD                | Andorra
AE                | United Arab Emirates
AR                | Argentina
------------------------------------------

currency

CurrencyCode      | Name                 
------------------------------------------
AED               | Emirati Dirham
ARS               | Argentine Peso
ALL               | Lek
------------------------------------------

countryregioncurrency

CountryRegionCode | CurrencyCode                
------------------------------------------
AE                | AED
AR                | ARS
------------------------------------------

How would I combine them such that the rows are interconnected like below:

Name                 | Name
------------------------------------------
United Arab Emirates | Emirati Dirham
Argentina            | Argentino Peso
------------------------------------------

score 1 · Answer 1 · answered Sep 27 '20 at 19:40

You could use two joins, where the countryregioncurrency table is used as a mapping table:

SELECT cr.name, c.name
FROM   countryregion cr
JOIN   countryregioncurrency crc ON cr.countryregioncode = crc.countryregioncode
JOIN   currency c ON c.currencycode = crc.currencycode

How to join tables in SQL by ID in another table?

1 Answers1