Sunday, 18 August 2013

hwo to? Selelect from one table where difference with (sum) from another table?

hwo to? Selelect from one table where difference with (sum) from another
table?

Sorry if the title is nothing but suggestive, even in my head, the
question is hard to express.
So, I have two tables with following fields:
...
orders.orderID
orders.orderValue
...
payments.orderID
payments.payVal
In payments.payVal there will be incremental payments for each order
(many-to-one).
What I need it so select ALL orders from where there is payment left
(orders.orderValue - ((sum)payments.payVal) > 0 ).
The only thing I can come up to right now is a (foreach) using the
orderID, but I cannot do that for some particular reasons. I also cannot
add a column inside table to hold the value for some reasons too.
What I need, is to perform the entire selection in one single SQL Query
something that resembles this idea: SELECT * FROM orders WHERE <...
each(orderValue - (sum(payVal))) > 0 ...>
Thank you... and I hope you won't find my struggle as "stupid", after all
I'm here to ask, to learn.

No comments:

Post a Comment