title: select reference value based on range
body: <p>I have three tables as below, with one of them having the reference values. I need to count the size, 2 rows smaller than 20 for example, and then multiply it by the &quot;smallerThan20&quot; value in the reference table as a col in the output table: 2*20. I can think of how to count based on a range from table1, for example <code>sum (case when t1.size &lt;20 then 1.0 else 0 end) as rownumlessthan20</code>, but really have no idea how to look up or join the second one. Many thanks for your help.</p>
  <p>product info:</p>
  <pre><code>+-----------+---+
  |. product  |size| 
  +-----------+---+
  |      apple| 10|
  |     orange| 5 |
  |       pear| 30|
  </code></pre>
  <p>reference info:</p>
  <pre><code>+---------------+------------+-------------
  | smallerThan20 |largerThan20| largerThan50
  +---------------+------------+------------
  |      10       |     20     |.    30
  </code></pre>