title: SQL to reconcile two tables
body: <p>I have two tables ItemSold and ItemInventory, and am trying to create a query that will check if the ItemSold SUM(quantity of an item) is balanced against ItemInventory. </p>
  
  <pre><code>ItemSold
  sale_id | item_id | quantity | conversion |
  --------  -------   ---------  ----------  
    1099       1         1.00       1.00
    1099       1         1.00       0.50
    1099       2         1.00       1.00
  </code></pre>
  
  <p>I'm using the conversion column to signify a retail/smaller unit of an item</p>
  
  <pre><code>ItemInventory
    id |   description | quantity
  -----   ------------   --------
    1        Item1         100.00
    2        Item2         100.00
  </code></pre>
  
  <p>Below is a query that I created to check if there are items that if you SUM up is greater than the quantity on the ItemInventory. </p>
  
  <pre><code>SELECT ItemSold.item_id, ItemSold.quantity, ItemSold.conversion
  FROM ItemSold 
  INNER JOIN ItemInventory 
  ON ItemSold.item_id = ItemInventory.id
  WHERE Itemsold.sale_id = @saleid
  GROUP BY ItemSold.item_id, ItemSold.quantity, ItemSold.conversion 
  HAVING SUM(ItemSold.quantity * ItemSold.conversion) &gt; SUM(ItemInventory.quantity)
  </code></pre>
  
  <p>It works but it compares the quantity of EACH ROW on the ItemSold rather than the SUM(quantity group by item_id).</p>
  
  <p>My goal is to not process the Sale transaction unless there is enough inventory regardless what unit the item is sold.</p>