join sql

Question

There are two tables:
for example:
tblCurrencies
CurrencyID,  Name
1  USD
2  GBP
3  CAD
4  JPY

tblCurrencyPairs
CurrencypairID,  Currency1ID,  Currency2ID
1   4  1
2   1  3
5   2  1

How can I write a sql query to show the fields in tblCurrencypairs, but to show the currencyNames concatenated instead of their IDs. So table tblCurrencyPairs should show something like:
CurrencypairID,  CurrencyPairs
1   JPYUSD
2   USDCAD
5   GBPUSD

Thanks

Answer 1

select 
 tblCurrencyPairs.CurrencypairID,  
 a.name + b.name
FROM
 tblCurrencies a ,tblCurrencies b ,tblCurrencyPairs
WHERE
 tblCurrencyPairs.Currency1ID = a.id
 AND
 tblCurrencyPairs.Currency2ID = b.id