MySQL Forums
Forum List  »  Newbie

Determine Balance Due from Two Tables
Posted by: Courtney Marton
Date: June 30, 2005 12:45PM

I am trying to come up with a query which will display the balance due for all of my clients. I have a charges table (tblregistration) and a payments table, both with foriegn key clientid. I want to sum the charges for the client, sum the payments for the client and display the difference between them as their current balance.

Here is the query I am using:

select tblclient.clientid, concat(tblclient.lastname, ", ",tblclient.firstname) as name,
sum(tblregistration.amount) as totalcost, sum(tblpayment.amount) as totalpaid,
sum(tblregistration.amount) - sum(tblpayment.amount) as balance
from tblclient, tblregistration, tblpayment
where tblclient.clientid = tblregistration.clientid
and tblclient.clientid = tblpayment.clientid
group by tblclient.clientid
order by name

I am not getting the correct results for those clients who have more than one row in either of the two tables.

What is the correct syntax?

Thanks!
Courtney

Options: ReplyQuote


Subject
Written By
Posted
Determine Balance Due from Two Tables
June 30, 2005 12:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.