title: Combine Tables Given Date Criteria
body: <p>I'm trying to combine to two tables, and I want the smallest id where the date from one table is greater than the date from another table.  I think this is an easy task, but it's giving me a fit.</p>
  
  <p>Here was my naive attempt:</p>
  
  <pre><code>select table1.id, MIN(table2.id)
  
  FROM table1
     INNER JOIN table2
       ON table2.id = table1.id
  
  WHERE table1.date &gt; table2.date
  </code></pre>
  
  <p>This doesn't work, because once the date in table1 is greater than table2 it joins all the ids not just the ones greater than the date.  Meaning that it returns the smallest id no matter what.</p>
  
  <p>Thanks</p>
  
  <p>EDIT FOR CLARITY:</p>
  
  <p>Product</p>
  
  <pre><code>id | price_date
  ---------------
   1 | 2011-01-02
  </code></pre>
  
  <p>Price</p>
  
  <pre><code>id | fid | price_date
  ---------------
   1 |  1  | 2011-01-01
   2 |  1  | 2012-01-01
  </code></pre>
  
  <p>So I want to return </p>
  
  <pre><code>product.id | price.id
  ---------------------
      1      |   1
  </code></pre>