title: Iterative SQL queries
body: <p>I am trying to develop a reasonably simple purchase order tracking database.
  I am having trouble visualising what it the most efficient way of doing this.</p>
  
  <p>At the moment I have 4 tables:</p>
  
  <pre><code>Purchase Order table:
  PO Number, Customer Product Number, Quantity
  6874     , ABC-0001                 4
  6873     , XYX-2222                 1
  
  Customer Product Table:
  Customer Product Number, Finished Goods Number
  ABC-0001               , 501-123
  ABC-0001               , 501-124
  ABC-0001               , 501-125
  
  
  Finished Goods Table:
  Finished Goods Number, Component Number, QTY Per FG
  501-123              , COMP-0001       , 1
  501-123              , COMP-0004       , 16
  501-123              , COMP-0009       , 12
  501-124              , COMP-0005       , 5
  501-124              , COMP-0003       , 9
  501-124              , COMP-0001       , 10
  501-125              , COMP-0006       , 3
  501-125              , COMP-0004       , 2
  501-125              , COMP-0003       , 1
  
  Component Table, Suppplier ID, (etc. etc. etc.)
  COMP-0001
  COMP-0002
  COMP-0003
  COMP-0004
  COMP-0005
  COMP-0006
  COMP-0007
  COMP-0008
  COMP-0009
  COMP-0010
  </code></pre>
  
  <p>I need to generate a list of all the individual components required to make a particular order.
  So the list would look something like this:</p>
  
  <pre><code>COMP-0001 - 44 (order quantity = 4 * (1 in 501-123 + 10 in 501-124)
  </code></pre>
  
  <p>etc. etc.</p>
  
  <p>Can this be done in SQL only, or do I have to do it in steps using Cursors and generating intermediate tables between steps?
  It seems like a pretty simple thing to do, but I haven't been able to find a single example of how to do it.</p>
  
  <p>The tables are reasonably large.</p>
  
  <pre><code>Order table is around 6000 orders (some of which are already complete, typically 300 or so currently open)
  Customer Product Table around 2000 items
  Finished Goods around 2000
  The component table contains over 20,000 separate components
  </code></pre>
  
  <p>Am I going about this the right way to achieve the result?</p>
  
  <p>Any help gratefully received.</p>