获取两个表并根据帐户计算总计

I have two tables, one is accounts, other is account_trans, accounts tables has records about accounts of individuals, and in account_trans tables, all transaction inserted according to the account id.

accounts tables has these,

account_id, account_holder_name, .....

and account_trans tables has these fields,

trans_id, account_id, debit_amount, credit_amount ....

now i wants to display all accounts at single page, and also i wants to display balance (credit-debit=total-balance) of each account. and i wants to use single mysql query.

the out put should be like this

Account Holder Name |  Account Balance (Credit Amount - Debit Amount = Balance Amount)

Account Holder Name2 |  Account Balance2 (Credit Amount2 - Debit Amount2 = Balance Amount2)

any suggestions for this...

There are many ways to do it. One of them is as follows. I am assuming that one account_id would have many debits and credits in the account_trans table.

Try this -

 SELECT accounts.account_holder_name
        (acc.credit - acc.debit) AS balance
 FROM accounts,
      (SELECT account_id
              SUM(IFNULL(debit_amount, 0)) debit,
              SUM(IFNULL(credit_amount, 0)) credit
       FROM account_trans
       GROUP BY account_id) acc
 WHERE accounts.account_id = acc.account_id