Hi there!
I have some problems with the complex MySQL join operations. In most cases, LEFT JOIN fulfills my needs but sometimes it doesn't work in the expected way. I'll give an example:
orders ------ - orderid - customerid
customers --------- - id - customername
orderitems ---------- - itemid - orderid - productid - quantity
shippeditems ------------ - shippingid - orderid - productid - shippedqty
Now I try to find out a list of orders, giving also the quantity of shipped and unshipped products in the list.
SELECT o.*,c.customername, SUM(oi.quantity) orderedTotal, SUM(si.shippedqty) shippedTotal
FROM orders o
LEFT JOIN customers c ON (c.id = o.customerid)
LEFT JOIN orderitems oi ON (oi.orderid = o.orderid)
LEFT JOIN shippeditems si ON (si.orderid = o.orderid)
GROUP BY o.orderid ORDER BY o.orderid;
This query returns all other information correct but the SUM functions return too large numbers.
Why I'm not able to user "normal" JOIN, is that I need also order information in that case that no items are shipped.
Thanks for your tips!
Ville
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
