Correlated SQL Join Query from multiple tables

Question

I have two tables like the ones below. I need to find what exchangeRate was in effect at the dateOfPurchase. I've tried some correlated sub queries, but I'm having difficulty getting the correlated record to be used in the sub queries.

I expect a solution will need to follow this basic outline:

1. SELECT only the exchangeRates for the applicable countryCode
2. From 1. SELECT the newest exchangeRate less than the dateOfPurchase
3. Fill in the query table with all the fields from 2. and the purchasesTable.

My Tables:

purchasesTable:

> dateOfPurchase    |   costOfPurchase  |   countryOfPurchase
> 29-March-2010 |   20.00       |   EUR
> 29-March-2010 |   3000        |   JPN
> 30-March-2010 |   50.00       |   EUR
> 30-March-2010 |   3000        |   JPN
> 30-March-2010 |   2000        |   JPN
> 31-March-2010 |   100.00      |   EUR
> 31-March-2010 |   125.00      |   EUR
> 31-March-2010 |   2000        |   JPN
> 31-March-2010 |   2400        |   JPN

costOfPurchase is in whatever the local currency is for a given countryCode

---

exchangeRateTable

> effectiveDate |   countryCode |   exchangeRate    
> 29-March-2010 |   JPN     |   90
> 29-March-2010 |   EUR     |   1.75
> 30-March-2010 |   JPN     |   92
> 31-March-2010 |   JPN     |   91

---

The results of the query that I'm looking for:

> dateOfPurchase    |   costOfPurchase  |   countryOfPurchase   |   exchangeRate    
> 29-March-2010 |   20.00       |   EUR         |   1.75
> 29-March-2010 |   3000        |   JPN         |   90
> 30-March-2010 |   50.00       |   EUR         |   1.75
> 30-March-2010 |   3000        |   JPN         |   92
> 30-March-2010 |   2000        |   JPN         |   92
> 31-March-2010 |   100.00      |   EUR         |   1.75
> 31-March-2010 |   125.00      |   EUR         |   1.75
> 31-March-2010 |   2000        |   JPN         |   91
> 31-March-2010 |   2400        |   JPN         |   91

So for example in the results, the exchange rate, in effect for EUR on 31-March was 1.75.

I'm using Access, but a MySQL answer would be fine too.

UPDATE:

Modification to Allan's answer:

SELECT dateOfPurchase, costOfPurchase, countryOfPurchase, exchangeRate
FROM purchasesTable p 
     LEFT OUTER JOIN 
     (SELECT e1.exchangeRate, e1.countryCode, e1.effectiveDate, min(e2.effectiveDate) AS enddate
      FROM exchangeRateTable e1 
           LEFT OUTER JOIN
           exchangeRateTable e2
           ON e1.effectiveDate < e2.effectiveDate AND e1.countryCode = e2.countryCode
     GROUP BY e1.exchangeRate, e1.countryCode, e1.effectiveDate) e 
     ON p.dateOfPurchase >= e.effectiveDate AND (p.dateOfPurchase < e.enddate OR e.enddate is null) AND p.countryOfPurchase = e.countryCode 

I had to make a couple small changes.

Answer 1

If your exchangeRate table doesn't have any gaps, this is pretty simple:

select dateOfPurchase, costOfPurchase, countryOfPurchase, exchangeRate
from purchasesTable p 
     inner join 
     exchangeRateTable e 
     on p.dateofpurchase = e.effectivedate 
        and p.countryofpurchase = e.countrycode 

If it does have gaps (the effective rate is set on 1/1 and doesn't change until 1/3, so the 1/1 rate applies to 1/2), then it gets a little more complicated because the end date is only ever implied. In that case, the following should work:

select dateOfPurchase, costOfPurchase, countryOfPurchase, exchangeRate
from purchasesTable p 
     left outer join 
     (select e1.exchangeRate, e1.countrycode, 
             e1.effectivedate, min(e2.effectivedate) as enddate
      from exchangeRateTable e1 
           left outer join 
           exchangeRateTable e2
           on e1.effective_date < e2.effective_date 
              and e1.countrycode = e2.countrycode
           group by e1.exchangeRate, e1.countrycode, 
              e1.effectivedate) e 
     on p.dateofpurchase >= e.effectivedate
        and (p.dateofpurchase < e.enddate
             or e.enddate is null) 
        and p.countryofpurchase = e.countrycode 

If you need to use this solution, you might want to put the innermost query in a stored query, both to simplify this and to make the end-date available to other processes.

---

What we're doing is getting each of the records from the exchange rate table (e1) and joining it to all of the entries in the same table (e2) that occur later in time. The we take the smallest of that second set of values (min(e2.effectivedate)).

Let's say you have only three values:

1/1/2000
1/3/2000
1/5/2000

The join will give you the following results (each value combined with all greater values):

1/1/2000 < 1/3/2000
1/1/2000 < 1/5/2000
1/3/2000 < 1/5/2000
1/5/2000 < [null]

Since there's no value that 1/5/2000 is lesser than and we specified an outer join, that row will have an empty value for the second table. We then specified that we only wanted the smallest value from the second table, so the result set is reduced to:

1/1/2000 < 1/3/2000
1/3/2000 < 1/5/2000
1/5/2000 < [null]

Finally, in the outermost join, we tell the query to join all dates between those two values. However, because one set has a null end date, we add an or condition to ignore the upper bound in that case.

---

I got started learning SQL from Litwin, et.al.'s "Access 95 Developer's Handbook" and reading Usenet a lot, so my sources are bit out of date...