Using Firebird, I have two tables, Orders
and DespatchedParts
.
Orders
table:
ID CustomerID Total_number_parts
-- ---------- ------------------
1 1 5
2 1 4
3 2 10
4 1 5
5 3 20
DespatchedParts
table:
ID OrdersID PartQty
-- -------- -------
1 1 2
2 1 3
3 2 10
4 3 10
5 3 10
So in the Orders
table the sum of Total_number_parts
for Cust 1's orders is 5+4+5=14.
Then looking in DespatchedParts
, we want the sum of PartQty
for Customer 1's, so 2+3=5.
So Customer 1 has ordered 14 parts and 5 have been dispatched. I want to get all the Customer ID's who have received all their parts.
Below is what I have got so far. I don't know where to put the sum(total_number_parts) = sum(qtyparts)
part.
Select a.customerID, sum(a.Total_number_parts)
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
on B.customerID = a.customerID
Group By a.CustomerID
Best Answer
You want to select all customers whose orders have been completely fulfilled. So first you need to know how many parts the customer has ordered in total:
You also need to know how many parts have been shipped for each customer. You will need to join orders and dispatched parts to get the customerid associated with the dispatched parts.
I'm using a left join here, so you also get the total quantity if no parts have been dispatched yet. This is not necessary for your question, but becomes relevant if you want to get all customers who haven't received all their parts yet.
We combine these two, and apply the requested condition in the where-clause:
Assuming the corrected orderids (1, 1, 3, 5, 5) I suggested in the comments, this yields
Alternatively, you could move the second part entirely into the where-clause by making it a correlated sub-query: