join sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

join sql

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-01-10 : 11:26:58

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 11:43:24

select cp.currencypairid, t1.name + t2.name
from tblcurrencypairs as cp
inner join tblcurrencies as t1 on t1.currencyid = cp.current1id
inner join tblcurrencies as t2 on t2.currencyid = cp.current2id
order by cp.currencypairid

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources