Scott Haneda <[EMAIL PROTECTED]> wrote on 02/22/2006 01:47:38 AM:
> Got myself a little stumped here, 4.0.18-standard
>
> Three tables in this mess, orders, order_items and products.
>
> orders.prod_id = order_items.prod_id = products.prod_id is how I relate
them
> all to each other.
>
> order_items has a quantity field.
>
> I need a report that shows me all the products with a sum() for each,
but
> only if the status of the order is NOT IN ('cancelled', 'pending',
'ghost')
>
> This gets me pretty close, but does not mask out the orders that have
the
> wrong status, as I do not know how to add in the join on orders
>
> SELECT p.prod_name, count(oi.product_id) as mycount
> FROM products as p
> LEFT JOIN order_items as oi
> on (p.id = oi.product_id)
> group by oi.product_id order by mycount;
> --
> -------------------------------------------------------------
> Scott Haneda Tel: 415.898.2602
> <http://www.newgeo.com> Novato, CA U.S.A.
>
>
Shouldn't there be a relationship between orders and order_items like
`orders`.`id` = `order_items`.`order_id`
or something similar? That would make better sense to me.... Making each
`order` product-specific doesn't sound like a good design especially when
each `order` looks like it can contain multiple `order_item`s, each
potentially for a different `product`.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
