title: How to select a quantity of rows from one table based on quantities from another
body: <p>So with this select statement (excluding joins and where clauses)</p>
  
  <pre><code>SELECT [ProductNo], (mf_btol.OrderedBoxes - mf_btol.ActualBoxes) as [Remaining], [DespatchOrderNo]
  </code></pre>
  
  <p>This gives a list of remaining products and quantity of boxes required to complete an order.</p>
  
  <p>What I want to do is select and display from a separate stock table an amount of box records based on how many of each product is required so I will be able to create a list of boxes to pick for the order.</p>
  
  <p>I think I need to be using <code>select top...</code> and use <code>order by</code> to prioritize the boxes to be picked but can't imagine how to get the quantities in there.</p>
  
  <p>Here is some made up data, so the results of the select above would be along the lines of this</p>
  
  <pre><code>ProductNo     Remaining      DespatchOrderNo
  845121         3             6194
  989222         2             6194
  </code></pre>
  
  <p>What I am wanting is</p>
  
  <pre><code>DOTNo     ProductNo      DespatchOrderNo
  123456    845121         6194
  123457    845121         6194
  123458    845121         6194
  124444    989222         6194
  124445    989222         6194
  </code></pre>
  
  <p>So its basically creating a list of individual boxes on a stock table (unique by dot number) required to forfill the orders from the first select statement for multiple products.</p>