List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 22 2006 2:11pm
Subject:Re: Inner join with left join
View as plain text  
Scott Haneda <lists@stripped> 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


Thread
Inner join with left joinScott Haneda22 Feb
  • Re: Inner join with left joinSGreen22 Feb
  • MySQL Queries within OscommerceAM COMS22 Feb
    • Re: MySQL Queries within OscommerceSGreen22 Feb
  • Re: Inner join with left joinPeter Brawley22 Feb
    • Re: Inner join with left joinScott Haneda22 Feb
      • Re: Inner join with left joinSGreen22 Feb
        • Re: Inner join with left joinScott Haneda23 Feb
          • Re: Inner join with left joinJames Harvard23 Feb
            • Re: Inner join with left joinScott Haneda23 Feb
            • Re: Inner join with left joinSGreen23 Feb
              • Re: Inner join with left joinScott Haneda23 Feb
                • Re: Inner join with left joinSGreen23 Feb
                  • Re: Inner join with left joinSGreen23 Feb
                • Re: Inner join with left joinPeter Brawley23 Feb
      • Re: Inner join with left joinPeter Brawley25 Feb