title: MYSQL Select records with closest date
body: <p>I am having the following 2 tables in my database:</p>
  
  <p>product_price</p>
  
  <pre><code>CREATE TABLE `product_price` (
    `asin` varchar(10) NOT NULL,
    `date` date NOT NULL,
    `price` decimal(7,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`asin`,`date`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>product_info </p>
  
  <pre><code>CREATE TABLE `product_info` (
    `asin` varchar(10) NOT NULL,
    `name` varchar(200) DEFAULT NULL,
    `brand` varchar(50) DEFAULT NULL,
    `part_number` varchar(50) DEFAULT NULL,
    `url` text,
    `image` text,
    PRIMARY KEY (`asin`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  </code></pre>
  
  <p>I want to find the products which its price has been reduced between a start date and a end date.</p>
  
  <p>I am using the following query right now which works:</p>
  
  <pre><code>$query      = "SELECT pi.*, prev.price AS 'old_price', curr.price, ROUND((100.0*(curr.price - prev.price) / prev.price),0) As PercentDiff FROM product_price As curr 
         JOIN product_price As prev ON curr.date = '".$end_date."' AND prev.date = '".$start_date."' 
         JOIN product_info pi ON curr.asin = pi.asin WHERE curr.asin = prev.asin HAVING PercentDiff &lt; 0 ORDER BY PercentDiff";
  </code></pre>
  
  <p>But the problem is that table product_price has not has a record if the price has not changed.</p>
  
  <p>Eg for example </p>
  
  <pre><code>asin        date         price
  AAAAAAAAA  2018-07-17     7
  AAAAAAAAA  2018-07-15     6
  </code></pre>
  
  <p>start_date= "2018-07-16" end_date="2018-07-17"</p>
  
  <p>I want it to have it in my results as price was reduced.</p>
  
  <p>How i had to change my query??</p>
  
  <p>I tried to change prev.date = '".$start_date."' to prev.date &lt;= '".$start_date."'</p>
  
  <p>But returns all the records and not the closest one as i want. </p>
  
  <p>Here is some sample data from product_price:</p>
  
  <pre><code>+------------+------------+--------+
  | asin       | date       | price  |
  +------------+------------+--------+
  | B000GBKDB4 | 2018-07-02 |  38.66 |
  | B000GBKDCI | 2018-07-02 |  72.98 |
  | B000GBKFLW | 2018-07-02 |  33.27 |
  | B000GBKFMG | 2018-07-02 |  63.45 |
  | B000GBLZEI | 2018-07-02 |  34.90 |
  | B000GBLZEI | 2018-07-04 |  21.31 |
  | B000GBLZEI | 2018-07-05 |  20.24 |
  | B000GBLZEI | 2018-07-06 |  18.41 |
  | B000GBLZEI | 2018-07-08 |  17.49 |
  | B000GBLZEI | 2018-07-09 |  15.79 |
  | B000GBLZEI | 2018-07-11 |  14.84 |
  | B000GBLZEI | 2018-07-16 |  14.29 |
  </code></pre>