title: SQL Join table with calculated fields
body: <p>I want to add another column to this query to show backorders that need to be processed.</p>
  
  <p>Currently the table is showing all orders that have outstanding items and the in stock amount for each stock ref included.</p>
  
  <p>I want to to add a field for suggested invoice amount. 
  I.e if the order qty is 5 , the invoice qty is 2 and the in stock qty is 2 then suggest to deliver 2.</p>
  
  <p>if the instock amount was 100 then suggest to deliver 3.</p>
  
  <p>My current attempt is below but I cant work out how to do the last bit.</p>
  
  <p>I know i need to do (qtyOrdered-qtyinvoiced) but literally hit a brick wall.</p>
  
  <pre><code>SELECT 
      c.cus_name,
      ol.orderLines_stockRef,
      ol.orderLines_qtyOrdered,
      ol.orderlines_qtyInvoiced,
      pr.products_instock
  FROM
      cus c
          JOIN
      orderheader oh ON oh.orderHeader_customer = c.cus_id
          JOIN
      orderlines ol ON ol.orderLines_orderId = oh.orderHeader_id
          JOIN
      products pr ON ol.orderlines_stockref = pr.products_reference
  WHERE
      ol.orderLines_qtyOrdered &lt;&gt; ol.orderlines_qtyInvoiced
  </code></pre>
  
  <p>Current output : </p>
  
  <p><a href="https://i.stack.imgur.com/UZJbq.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UZJbq.jpg" alt="Output"></a></p>