Correlated SQL Join Query from multiple tables
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 A开发者_Go百科ND 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.
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...
精彩评论