title: Conditional JOIN produces two many rows
body: <p>I am trying to join two tables like these:</p>
  
  <p>Table A</p>
  
  <pre><code>A1, A2, A3
  A, 1, 35
  A, 1, 70
  A, 1, 105
  </code></pre>
  
  <p>Table B</p>
  
  <pre><code>B1,B2, B3
  B, 1, 30
  C, 1, 32
  D, 1, 40
  E, 1, 55
  F, 1, 60
  G, 1, 77
  H, 1, 80
  </code></pre>
  
  <p>The expected result is like this:</p>
  
  <pre><code>A, 1, 35, C, 32
  A, 1, 70, F, 60
  A, 1, 105, H, 80
  </code></pre>
  
  <p>That is, for every row in table A, I want to pick just one row from table B, which should be the row with the value of B3 smaller, but closest to the value of A3.</p>
  
  <p>So far, I have tried the following query:</p>
  
  <p><code>SELECT A1, A2, A3, B1, B3  FROM A JOIN (SELECT B1, B2, B3  FROM B ORDER BY B2, B3 DESC) AS A ON A.A2 = B.B2 AND A.B3 &lt; A.A3</code> </p>
  
  <p>However, this results in a table as follows:</p>
  
  <pre><code>A, 1, 35, B, 30
  A, 1, 35, C, 32
  A, 1, 70, B, 30
  A, 1, 70, C, 32
  A, 1, 70, D, 40
  A, 1, 70, E, 55
  </code></pre>
  
  <p>and so forth.  I also tried to add a LIMIT 1 to the inner SELECT, but that does not produce any results.  If I run the inner query by itself (with LIMIT 1), I do get the expected result.  How can I change the query to produce the desired result?  I am using sqlite at the moment, but would like to write this in a way that is portable to other DBMS.</p>