title: MySQL: Showing only orders that have a balance due when there are multiple bill tables for each order (and multiple payments)
body: <p>My goal is to get a (relatively efficient) MySQL view that shows all orders that still have an outstanding balance--showing the invoice amount, the sum of all storage fees, total amount paid, and total due for each order. The two "bills" are the original invoice amount from the orders table and then the sum of all storage amounts (fees) from the storage table.</p>
  
  <p>There is the invoice amount in the orders table, all the payments for particular orders in the payments table and all the extra storage fees for particular orders in the storage table (the payment and storage tables are related to the orders table on order_id field).</p>
  
  <p>Below is my schema with example data (and here is the <a href="http://sqlfiddle.com/#!9/8e6f3" rel="nofollow">SQL fiddle</a> if that is helpful): </p>
  
  <pre><code>
  CREATE TABLE orders
  (
  order_id int NOT NULL AUTO_INCREMENT,
  invoice decimal(10,2),
  PRIMARY KEY (order_id)
  );
  
  
  CREATE TABLE payments
  (
  payments_id int NOT NULL AUTO_INCREMENT,
  order_id int,
  amount decimal(10,2),
  PRIMARY KEY (payments_id)
  );
  
  CREATE TABLE storage
  (
  storage_id int NOT NULL AUTO_INCREMENT,
  order_id int,
  amount decimal(10,2),
  PRIMARY KEY (storage_id)
  );
  
  
  INSERT INTO orders (invoice)
  VALUES ('250'), (NULL), ('150'), ('175.00'), ('175.00'), (NULL);
  
  INSERT INTO payments (order_id, amount)
  VALUES ('1', '50'), ('3', '50'), ('1', '100'), ('1', '150'), ('4', '100'), ('4', '25'), ('3', '50');
  
  INSERT INTO storage (order_id, amount)
  VALUES ('1', '50'), ('4', '25');
  </code></pre>
  
  <p>The following query works but because I repeat so much and it looks like a total of 5 subqueries, I am guessing this is not an efficient way to get this to work:</p>
  
  <pre><code>
  CREATE VIEW accounts_receivable AS
  SELECT
  orders.order_id AS orderid,
  orders.invoice,
  (SELECT sum(amount) from storage WHERE storage.order_id = orders.order_id) AS storagebill,
  coalesce(sum(payments.amount), 0) AS paid,
  coalesce(orders.invoice, 0) + (SELECT coalesce(SUM(storage.amount), 0) FROM storage WHERE storage.order_id = orders.order_id) - (SELECT coalesce(sum(payments.amount), 0) FROM payments WHERE orders.order_id = payments.order_id) AS due
  FROM orders
  LEFT JOIN payments on payments.order_id = orders.order_id
  WHERE
  (coalesce(orders.invoice, 0) + (SELECT coalesce(SUM(storage.amount), 0) FROM storage WHERE storage.order_id = orders.order_id) - (SELECT coalesce(sum(payments.amount), 0) FROM payments WHERE orders.order_id = payments.order_id)) > 0
  GROUP BY orders.order_id
  </code></pre>
  
  <p>Is there any way to get this data in a more efficient way? I am guessing there are some different ways of joining the data here but I have experimented (and searched for the answer) to no avail. I am relatively new to SQL.</p>