title: MySQL - Joining two tables with datetime columns on Date and three most recent entries before that date
body: <p>I have two tables in my SQL</p>
  
  <p>For example <strong>Table1 - ItemPrice</strong>:</p>
  
  <pre><code>DATETIME   |  ITEM | PRICE
  2011-08-28 |   ABC   123 
  2011-09-01 |   ABC   125
  2011-09-02 |   ABC   124 
  2011-09-03 |   ABC   127 
  2011-09-04 |   ABC   126
  </code></pre>
  
  <p><strong>Table2 - DayScore</strong>:</p>
  
  <pre><code>DATETIME   |  ITEM | SCORE
  2011-08-28 |   ABC    1
  2011-08-29 |   ABC    8
  2011-09-01 |   ABC    4
  2011-09-02 |   ABC    2
  2011-09-03 |   ABC    7  
  2011-09-04 |   ABC    3
  </code></pre>
  
  <p>I want to write a query, which given a item ID (e.g. <em>ABC</em>), will return the price at that date from <code>ItemPrice</code> (of there is no price for that date then the query should not return anything). If a valid price is found for the query date, the query should return (in 9 columns) </p>
  
  <ul>
  <li>the price of the item from <code>ItemPrice</code> for the past three days (i.e. the most recent 3 prices before the date queried). </li>
  <li>In the next three columns it should return, from <code>DayScore</code>, the matching score for those 3 dates selected from ItemPrice.</li>
  <li>Finally the dates (t-1 to t-3) selected</li>
  </ul>
  
  <p>In otherwords the results for this query looking at just date='2011-09-03' as an example for item='abc' would return:</p>
  
  <pre><code>DATE      |  ITEM  |  PRICE  |  SCR  | PRC_t-1 | PRC_t-2 | PRC_t-3 | SCR_t-1 | SCR_t-2 | SCR_t-3 | DATE_t-1  | DATE_t-2  | DATE_t-3 
  2011-09-03|  ABC   |  127    |  7    | 124     | 125     | 123     | 2       | 4       | 1       | 2011-09-02| 2011-09-01| 2011-08-28
  ....
  </code></pre>
  
  <p>Etc for each date that appears in <code>ItemPrice</code> table.</p>
  
  <p>What is the neatest and most efficient way to run this query (as its something that will be run over many millions of rows)?</p>
  
  <p>Cheers!</p>