Re: Determine Balance Due from Two Tables

[Jay Pipes]

Try:

SELECT
c.clientid
, concat(c.lastname, ", ",c.firstname) as name
, SUM(IFNULL(r.amount, 0)) as totalcost
, SUM(IFNULL(p.amount, 0)) as totalpaid
, (SUM(IFNULL(r.amount, 0)) -SUM(IFNULL(p.amount, 0))) as balance
FROM tblclient c
LEFT JOIN tblregistration r
ON c.clientid = r.clientid
LEFT JOIN tblpayment p
ON c.clientid = p.clientid
GROUP BY c.clientid
ORDER BY name

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com