Correlated SQL Join Query from multiple tables
- by SooDesuNe
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.
