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

[eluser]adwin[/eluser]
Hi ...

I need help to translate this from oracle statements into mysql (especially the joins)

Code:

// oracle sql
select b.goodsid, sum(b.total) as total_inv, sum(d.total) as total_ci
from invoicehd a , invoicedt b, creditInvHd c, creditInvDt d
where a.id between $inv1 and $inv2
and a.invdate between $date1 and $date2
and b.invoice_id = a.id
and c.invoice_id = a.id (+)
and d.ciid = c.id
and d.goods_id = b.goods_id (+)
groups by b.goodsid

I have no idea how to do left join like that on mysql ...
is it possible ? or should i create 2 query and then do a union .... :p

[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

[eluser]adwin[/eluser]
Thank you Seppo ...

I know right now about joining in mysql

I checked your script and the result was correct.

[eluser]Armchair Samurai[/eluser]
Just a quick note - there is a BETWEEN syntax in MySQL, so you could simplify Seppo's code somewhat:

Code:

# joins here...
WHERE a.id BETWEEN $inv1 AND $inv2
AND a.invdate BETWEEN $date1 AND $date2
GROUP BY b.goodsid

[eluser]Seppo[/eluser]
Thanks - I knew it existed but I didn't remember the syntax