left join confusion ... in mysql lol...

[eluser]Seppo[/eluser]
I'm not sure if all your joins are left - If any of them isn't just remove the "LEFT" word.

Code:

SELECT b.goodsid, sum(b.total) as total_inv, sum(d.total) as total_ci
FROM invoicehd a
LEFT JOIN invoicedt b ON b.invoice_id = a.id
LEFT JOIN creditInvHd c ON c.invoice_id = a.id
LEFT JOIN creditInvDt d ON d.ciid = c.id AND d.goods_id = b.goods_id

WHERE a.id >= $inv1
AND a.id <= $inv2
AND a.invdate >= $date1
AND a.invdate <= $date2
GROUP BY b.goodsid