Complex joining - multiple tables to one

[Ville Mattila]

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