Is this what you had in mind? It assumes MySQL version 4.1 or later: SELECT id, price - (SELECT SUM(IF(type = 'PAID', amount, 0 - amount)) FROM payment py WHERE py.id <http://py.id> = pr.id <http://pr.id>) AS balance FROM price pr;
On 8/4/05, Yesmin Patwary <[EMAIL PROTECTED]> wrote: > > > Dear All, > > > > I am trying to calculate balance for all id's. Can it be done using only > one MySQL query (without any scripting, eg: php,perl,asp..)? > > > > price table: > > id|price > > 1|150 > > 2|100 > > 3|300 > > > > payment table: > > id|transaction_type|amount > > 1|PAID|75 > > 1|PAID|25 > > 1|REFUND|60 > > 1|REFUND|30 > > 1|PAID|140 > > 2|PAID|35 > > 2|REFUND|15 > > 3|PAID|300 > > > > Thanks in advance for any help. > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com >
