title: Complex SQL Query for Inventory System
body: <p>I have 5 tables:  </p>
  
  <ol>
  <li><p>Items<br>
  <a href="https://i.stack.imgur.com/Qs1ga.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qs1ga.png" alt="enter image description here"></a>  </p></li>
  <li><p>Inventory<br>
  <a href="https://i.stack.imgur.com/R9ABa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R9ABa.png" alt="enter image description here"></a>  </p></li>
  <li><p>ConsumedItemsMonitoring<br>
  <a href="https://i.stack.imgur.com/Y7qgj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Y7qgj.png" alt="enter image description here"></a>  </p></li>
  <li><p>DamagedItemsMonitoring<br>
  <a href="https://i.stack.imgur.com/sbN5d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sbN5d.png" alt="enter image description here"></a>  </p></li>
  <li><p>UnaccountedItems<br>
  <a href="https://i.stack.imgur.com/GQ3mT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GQ3mT.png" alt="enter image description here"></a>  </p></li>
  </ol>
  
  <p>I'm new to Complex SQL queries did some research and asked for help and this is what I my code looks like so far.</p>
  
  <pre><code>SELECT Items.ItemID, Items.Item, 
  SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) &lt; CURDATE() THEN Inventory.Quantity ELSE 0 END) - 
  SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) &lt;CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 end) - 
  SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate)&lt;CURDATE() THEN damagedinventory.Quantity ELSE 0 end) - 
  SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate)&lt;CURDATE() THEN unaccounteditems.Quantity ELSE 0 end) AS 'PrevBalance',
  
  SUM(CASE WHEN DATE(Inventory.ItemTransactionDate)=CURDATE() THEN Inventory.Quantity else 0 END) AS 'DeliveredToday',
  
  SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate)=CURDATE() THEN damagedinventory.Quantity ELSE 0 END) AS 'DamagedToday',
  
  SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate)=CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 END) AS 'ConsumedToday',
  
  SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate)=CURDATE() THEN unaccounteditems.Quantity ELSE 0 END) AS 'UnAccountedToday',
  
  SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) &lt; CURDATE() THEN Inventory.Quantity else 0 end)-
  SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) &lt; CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 END)-
  SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) &lt; CURDATE() THEN damagedinventory.Quantity ELSE 0 END)-
  SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate) &lt; CURDATE() THEN unaccounteditems.Quantity ELSE 0 END)-
  SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) = CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 END)-
  SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) = CURDATE() THEN damagedinventory.Quantity ELSE 0 END)-
  SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate) = CURDATE() THEN unaccounteditems.Quantity ELSE 0 END) +
  SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) = CURDATE() then Inventory.Quantity ELSE 0 end) AS 'Total Balance' 
  
  FROM Items
  
  LEFT OUTER JOIN consumeditemmonitoring ON consumeditemmonitoring.ItemID = Items.ItemID 
  LEFT OUTER JOIN damagedinventory ON damagedinventory.ItemID = Items.ItemID
  LEFT OUTER JOIN unaccounteditems ON unaccounteditems.ItemID = Items.ItemID
  LEFT OUTER JOIN inventory ON inventory.ItemID= Items.ItemID
  GROUP BY Items.ItemID
  </code></pre>
  
  <p>The output looks like some of the table are multiplied.<br>
  <a href="https://i.stack.imgur.com/Pwz5F.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Pwz5F.png" alt="enter image description here"></a></p>