+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: left join confusion ... in mysql lol... (/showthread.php?tid=8813)
left join confusion ... in mysql lol... - El Forum - 06-01-2008
[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.goodsidI 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
left join confusion ... in mysql lol... - El Forum - 06-02-2008
[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.goodsidleft join confusion ... in mysql lol... - El Forum - 06-02-2008
[eluser]adwin[/eluser]
Thank you Seppo ...
I know right now about joining in mysql
I checked your script and the result was correct.
left join confusion ... in mysql lol... - El Forum - 06-02-2008
[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.goodsidleft join confusion ... in mysql lol... - El Forum - 06-03-2008
[eluser]Seppo[/eluser]
Thanks - I knew it existed but I didn't remember the syntax