title: Do I Need a Subquery on SQL View?
body: <p>I created the first part of the query which shows the total quantity of a product currently waiting to be sent out to customers &amp; returns me the item number from the <code>Item</code> table - </p>
  
  <pre><code>SELECT
    [Item].[ItemNum],
    SUM([Sales].[Quantity]) AS [Qty]
  FROM [dbo].[Item]
  LEFT OUTER JOIN [dbo].[Sales]
    ON [Item].[ItemNum] = [Sales].[ItemNum]
  WHERE ([Sales].[Sale Type] = 1)
  GROUP BY [Item].[ItemNum]
  </code></pre>
  
  <p>That works great, I get the catalogue number of the item and how many are on order.  Now I need to join to another table, which shows me stock of items.  This is what the query looks like - </p>
  
  <pre><code>SELECT
    [Item].[ItemNum] SUM ([Sales].[Quantity]) AS [Qty], SUM ([Stock].[Remaining Quantity])
  AS [Free Stock]
  FROM [dbo].[Item]
  LEFT OUTER JOIN dbo.[Sales]
    ON [Item].[ItemNu]m = [Sales].[ItemNum]
  LEFT OUTER JOIN [dbo].[Stock]
    ON [Item].[ItemNum] = [Stock].[ItemNum]
  WHERE ([Sales].[Document Type] = 1)
  GROUP BY [Item].[ItemNum]
  </code></pre>
  
  <p>Now the quantity on order is way out and the quantity in stock is wrong as well.  As I have the 'where' applied to the sales table but it doesn't apply to the stock table, do I need to move it out?</p>
  
  <p>Thanks</p>