List:General Discussion« Previous MessageNext Message »
From:Ville Mattila Date:January 3 2005 12:18pm
Subject:Complex joining - multiple tables to one
View as plain text  
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
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