Correlated SQL Join Query from multiple tables

Posted by SooDesuNe on Stack Overflow See other posts from Stack Overflow or by SooDesuNe
Published on 2010-03-30T18:33:30Z Indexed on 2010/03/30 20:53 UTC
Read the original article Hit count: 420

Filed under:

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:

SELECT only the exchangeRates for the applicable countryCode
From 1. SELECT the newest exchangeRate less than the dateOfPurchase
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.

Developer IT