List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 4 2005 3:18pm
Subject:Re: Complex joining - multiple tables to one
View as plain text  
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 <ville.mattila@stripped> 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/mysql?unsub=1
> 

Thread
use of soundex in queriesRaphael Matthias Krug31 Dec
  • Re: use of soundex in queriesSGreen31 Dec
    • Re: use of soundex in queriesRaphael Matthias Krug31 Dec
      • Re: use of soundex in queriesSGreen31 Dec
        • doing SELECTS and keeping the array intact with phpjulian haffegee3 Jan
          • Re: doing SELECTS and keeping the array intact with phpPeter Brawley3 Jan
          • Re: doing SELECTS and keeping the array intact with phpSebastian Burg3 Jan
      • Re: use of soundex in queriesSasha Pachev1 Jan
        • Re: use of soundex in queriesRaphael Matthias Krug1 Jan
          • Re: use of soundex in queriesSasha Pachev14 Jan
        • Complex joining - multiple tables to oneVille Mattila3 Jan
          • Re: Complex joining - multiple tables to oneSGreen4 Jan
  • Re: use of soundex in queriesKevin A. Burton3 Jan
RE: doing SELECTS and keeping the array intact with phpJay Blanchard3 Jan
RE: doing SELECTS and keeping the array intact with phpTom Crimmins3 Jan