You're left joining invoices, then left joining receipts. A left join will replicate rows on the left side to match the number of
rows it found in the join, or just leave 1 row with NULL values (as you probably know). This is where your problem is. You were
correct to try to use left joins because there may or may not be invoices/receipts.
Your invoice values are being replicated to match the number of receipts matched in the join. You need to summarize the invoices and
receipts separately since there is no relation between the two in this case. You also shouldn't be doing conditional sums.
That means breaking it up into essentially 2 queries and then summarizing the result of those two queries. It can still be one
query, just a little complicated. You're going to be querying and summarizing the result of the union of two other queries. Not sure
if I got everything correct in this query, but this type of query does work in mysql 4.
SELECT account.accountId, account.name,sum(debits) as debits, sum(credits) as
credits
FROM (
SELECT account.accountId, account.name,
sum(if(invoices.amount IS NULL,0,invoices.amount)) as debits,
0 as credits
FROM family
LEFT JOIN invoices ON invoices.accountId=account.accountId
WHERE invoices.invoiceDate<=CURDATE()
GROUP BY account.accountId
UNION
SELECT account.accountId, account.name,
0 as debits,
sum(if(receipts.amount IS NULL, 0, receipts.amount)) as credits,
FROM family
JOIN receipts ON receipts.accountId=account.accountId
GROUP BY account.accountId
) AS combined
GROUP BY account.acountId
----- Original Message -----
From: "webmaster" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, January 30, 2007 6:28 AM
Subject: Joins with sums
I'm missing something really silly in a query.
I want to produce a list of accounts, the invoices and receipts and the
balance against them.
Simply, there is an accounts table, invoices and receipts.
I want to show the account details, sum(invoices), sum(receipts) and the
balance. There is a situation where there maybe receipts and no invoices,
and visa-versa, hence the if statement.
SELECT account.accountId, account.name,
if(count(invoices.invoiceId)=0,0,sum(invoices.amount)) as debits,
if(count(receipts.receiptId)=0,0,sum(receipts.amount)) as credits,
if(count(invoices.invoiceId)=0,0,sum(invoices.amount))-if(count(receipts.rec
eiptId)=0,0,sum(receipts.amount)) as balance
FROM family
LEFT JOIN invoices ON invoices.accountId=account.accountId
LEFT JOIN receipts ON receipts.accountId=account.accountId
WHERE invoices.invoiceDate<=CURDATE()
GROUP BY account.accountId
For some reason, I'm getting results with multiple invoices etc against them
- normally 3. Can anyone point out where I've gone wrong?
Cheers
Nunners
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
