I hate SQL. One way of doing a subquery in a JOIN statement

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…

  1. We have generic things called Stores. Each store has a balance.
  2. We also have generic things called purchase orders, multiple of them per store, where each refers to the store by a foreign key.
  3. The purchase orders have an amount, but not a balance column.
  4. There are multiple stock received entries against every purchase order.
  5. 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
Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Database, SQL and tagged . Bookmark the permalink.

One Response to I hate SQL. One way of doing a subquery in a JOIN statement

  1. Roger W says:

    Yeap, and you could also user OUTER APPLY if the left side of the join was being repeated

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s