title: Select Exchange Rate based on Currency and Date
body: <p>I've looked around for a solution to this, but at least I was unable to find anything which would at least be similar to my case.</p>
  
  <p>I need to select the exchange rate, based on the date a product was purchased.
  Let me try and explain.</p>
  
  <p>I have a table with Currencies:</p>
  
  <pre><code>CREATE TABLE `tblCurrencies` (
    `CurrID` int(11) NOT NULL AUTO_INCREMENT,
    `CurencySymbol` varchar(1) DEFAULT NULL,
    `CurrencyCode` varchar(3) DEFAULT NULL,
    `CurrencyDescription` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`CurrID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>A table with Exchange Rates:</p>
  
  <pre><code>CREATE TABLE `tblExchRates` (
    `ExcID` int(11) NOT NULL AUTO_INCREMENT,
    `CurrKey` int(11) DEFAULT NULL,
    `Date` date DEFAULT NULL,
    `Exchange` decimal(11,3) DEFAULT NULL,
    PRIMARY KEY (`ExcID`),
    KEY `CurrKey` (`CurrKey`),
    CONSTRAINT `tblExchRates_ibfk_1` FOREIGN KEY (`CurrKey`) REFERENCES `tblCurrencies` (`CurrID`) ON DELETE RESTRICT ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>And a table with Products (note my products are listed in numbers in the table, which is OK in my case):</p>
  
  <pre><code>CREATE TABLE `tblProducts` (
    `ProductID` int(11) NOT NULL AUTO_INCREMENT,
    `Contract` int(11) DEFAULT NULL,
    `Product` int(11) DEFAULT NULL,
    `Type` varchar(100) DEFAULT NULL,
    `Currency` int(11) DEFAULT NULL,
    `Amount` decimal(10,0) DEFAULT NULL,
    `PurchaseDate` datetime DEFAULT NULL,
    PRIMARY KEY (`ProductID`),
    KEY `Contract` (`Contract`),
    KEY `Currency` (`Currency`),
    CONSTRAINT `tblShopCart_ibfk_2` FOREIGN KEY (`Currency`) REFERENCES `tblCurrencies` (`CurrID`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `tblShopCart_ibfk_1` FOREIGN KEY (`Contract`) REFERENCES `tblContracts` (`ContractID`) ON DELETE RESTRICT ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=3155 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>In the Exchange Rates table, as an example, values are set like this:</p>
  
  <pre><code>CurrKey              Date                 Exchange
  1                    15-01-2017           0.850
  1                    31-01-2017           0.856
  1                    02-02-2018           0.918
  1                    18-02-2018           0.905
  2                    04-02-2018           1.765
  2                    14-02-2018           1.755
  </code></pre>
  
  <p>And so on...</p>
  
  <p>I want to have a query that select a unique exchange rate based on the date a product was purchased and the currency it was purchased.</p>
  
  <p>In other words, as an example, if I have a product that was purchased on 07-02-2018, the query has to select the exchange rate which is valid in the relevant date rage that matches the purchase date and its currency. In this example, the correct exchange rage for a product purchased on 07-02-2018 which has a currkey of 1 would be 0.918</p>
  
  <p>Please note that exchange rates are set on random dates (as per example above).</p>
  
  <p>I managed to make this query, but it is not precise, as it sometimes returns two or more results (due to the 10 days range I set), whereas I only need 1 result: </p>
  
  <pre><code>SELECT
  tblExchRates.Exchange
  FROM
  tblCurrencies
  INNER JOIN tblExchRates ON tblExchRates.CurrKey = tblCurrencies.CurrID
  WHERE
  tblCurrencies.CurrencyCode = "EUR" AND
  tblExchRates.Date BETWEEN (tblProducts.PurchaseDate - INTERVAL 10 DAY) AND (tblProducts.PurchaseDate + INTERVAL 10 DAY)
  </code></pre>