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]
