It's probably your JOIN on shipped items producing a cross product with
your JOIN on ordered items. What happens if you take the entire
"shippeditems" JOIN out of your statement? Are your numbers still off?
Since your query is not using data from that table you should be able to
eliminate it from your join without introducing any errors.
You should be able to see the duplication of records if you run this
query:
SELECT o.orderid
, c.customerid
, oi.itemid
, oi.productid
, oi.quantity
, si.shippingid
, si.productid
, si.shippedqty
FROM orders o
INNER JOIN customers c
on c.id = o.customerid
LEFT JOIN orderitems oi
on o.orderid = oi.orderid
LEFT JOIN shippeditems si
on o.orderid = si.orderid
WHERE o.orderid = <some orderid with the wrong results>
I think that when you look at this you will see that some of the
orderitems and/or shippeditems will be duplicated.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Ville Mattila <[EMAIL PROTECTED]> wrote on 01/03/2005 07:18:16
AM:
> 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]
>
