I really do.
I’m writing this mostly for myself because I never remember this shit. I wrote this query now but I know that I won’t remember how to do this in future, so here is the public note to self for next time. (This is slightly changed from the entity names at work so that I can’t get into trouble.)
I want a simple query that returns a couple of fields, the cash on hand, and the sum of the balance of orders in progress for a particular store, given these conditions…
- We have generic things called Stores. Each store has a balance.
- We also have generic things called purchase orders, multiple of them per store, where each refers to the store by a foreign key.
- The purchase orders have an amount, but not a balance column.
- There are multiple stock received entries against every purchase order.
- The purchase order balance is thus the purchase order amount, minus the sum of all the stock received entries against that purchase order.
So I really need two select statements… One from Store and one from PurchaseOrders. The problem is the purchase order balance is calculated by joining with the stock received table, and aggregating, so I can’t do the whole thing in one simple query, or so I thought.
It turns out that you can do a subquery in a join statement, then simply alias it, and this worked for me… All I needed to do was add the store ID field to the subquery so that I could treat it just like a table and join against it. I’ve done something like this before, and forgotten how… hence this stupid post. Did I mention I hate SQL?
SELECT Store.Balance CashOnHand, ISNULL(SUM(t.Balance), 0) OrderInProgress FROM Store LEFT JOIN ( SELECT po.Amount - ISNULL(SUM(s.amount), 0) Balance, po.StoreID FROM PurchaseOrder po LEFT JOIN StockReceived s ON s.PurchaseOrderID = po.id WHERE po.StoreID = @StoreID GROUP BY po.ID, po.Amount, po.StoreID ) t ON t.StoreID = @StoreID WHERE Store.ID = @StoreID GROUP BY Store.Balance