Welcome Guest, Not a member yet? Register   Sign In
left join confusion ... in mysql lol...

Hi ...

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

// 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 ... Sad
is it possible ? or should i create 2 query and then do a union .... :p

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

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

Thank you Seppo ...

I know right now about joining in mysql Big Grin

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:
# joins here...
WHERE a.id BETWEEN $inv1 AND $inv2
AND a.invdate BETWEEN $date1 AND $date2
GROUP BY b.goodsid

Thanks - I knew it existed but I didn't remember the syntax

Theme © iAndrew 2016 - Forum software by © MyBB