Wednesday, March 7, 2012

join sql

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

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

No comments:

Post a Comment