Joining three Tables in Sql Server

SELECT
HOSTCurrencyAmt
HOSTCUrrencyCode
HOMECurrencyAmt
HOMECurrencyCode
FROM
ExpenseAmounts
Currencycode can be anything US Dollars, Indian Rupees, Euro etc etc.
Based on the currencycode we have to update the Amounts field for e.g.
IF HostCurrencyCode is US Dollars and HOSTCurrentAMt
is 5, then we have to update HOStCurrencyAMt to 5 * 46 rs. This 46 (The
current Exchange rate we will fetching from some other table like this
:
select ExchangeRate from tblforexrate ex
inner join tblcode tc
on ex.currcd=tc.cd
and tc.abbr='USD'
and tc.typ=1076
order by tb.cd
In the above query we have hardcoded currencycode to 'USD' but in real
we have to update the first table
ExpenseAmounts using join with the tblforexrate,tblcode taking current
exchangerate for the HOME and HOST Currencycodes
from the later two tables.
Can any one help me with this.
Regards,
RajeevRajeev (rajeev.rajput@.gmail.com) writes:
> SELECT
> HOSTCurrencyAmt
> HOSTCUrrencyCode
> HOMECurrencyAmt
> HOMECurrencyCode
> FROM
> ExpenseAmounts
>
> Currencycode can be anything US Dollars, Indian Rupees, Euro etc etc.
> Based on the currencycode we have to update the Amounts field for e.g.
> IF HostCurrencyCode is US Dollars and HOSTCurrentAMt
> is 5, then we have to update HOStCurrencyAMt to 5 * 46 rs. This 46 (The
> current Exchange rate we will fetching from some other table like this
>:
> select ExchangeRate from tblforexrate ex
> inner join tblcode tc
> on ex.currcd=tc.cd
> and tc.abbr='USD'
> and tc.typ=1076
> order by tb.cd
> In the above query we have hardcoded currencycode to 'USD' but in real
> we have to update the first table
> ExpenseAmounts using join with the tblforexrate,tblcode taking current
> exchangerate for the HOME and HOST Currencycodes
> from the later two tables.
If I get this right:
SELECT HOMECurrencyAMT = ex.ExchangeRate * ea.HOSTCurrencyAMT
FROM ExpenseAmounta ea
JOIN tblcode tc ON ea.HOSTCurrencyCODE = tc.abbr
AND tc.ytp = 1076
JOIN tblforexrate ex ON ex.currcd = tc.cd
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx