Two Selects ..... How? [message #18514] |
Sun, 03 February 2002 13:50 |
James Herriot
Messages: 1 Registered: February 2002
|
Junior Member |
|
|
Hi folks,
I am developing a finance program and I am having difficulty writing a SQL query to get the info that I need.
In short I have two tables:
TABLE 1: Currency Rates
-------------------------------------
FROM_CURRENCY (CURRENCY_ABRV)
TO_CURRENCY (CURRENCY_ABRV)
EXCHANGE_RATE
DATE
TABLE 2: Currency Country
----------------------------------------
CURRENCY_ABRV
CURRENCY_NAME
CURRENCY_SYMBOL
COUNTRY
REGION
COUNTRY_FLAG_IMAGE
I want to get the following information:
1. FROM_CURRENCY_FLAG
2. FROM_COUNTRY
3. TO_CURRENCY_FLAG
4. TO_COUNTRY
5. RATE
So basically using Table 1 FROM_CURRENCY & TO_CURRENCY and getting the relevant Country & Flag for each. The problem is that I cannot seem to get this info from one select statement. I at the moment I am getting all the records from Table 1. Then for each From & To currency searching Table 2.
This is very very time consuming. Anyone can help how to do this better.
Many thanks
James
|
|
|
Re: Two Selects ..... How? [message #18515 is a reply to message #18514] |
Sun, 03 February 2002 14:01 |
Mike
Messages: 417 Registered: September 1998
|
Senior Member |
|
|
You have to include the Currency Country table (table2) twice. Like:
select
cc_from.COUNTRY,
cc_to.COUNTRY,
cr.RATE
FROM
table1 cr,
table2 cc_from,
table2 cc_to
WHERE cr.FROM_CURRENCY = cc_from.CURRENCY_ABRV
AND cr.TO_CURRENCY = cc_to.CURRENCY_ABRV;
Hope that helps
Mike
|
|
|