List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 23 2006 6:08pm
Subject:Re: Inner join with left join
View as plain text  
I hate remembering crap like this AFTER I hit send...

Because we want to limit our sum() to only those rows that match the ORDER 
conditionals, we have to change our formula to recognized when to count 
and when to not count an order_item.

SELECT p.id, p.prod_name, sum(if(o.id is null,0,oi.quantity)) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
GROUP BY p.id, p.prod_name

I think I need a nap! --- SORRY!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

SGreen@stripped wrote on 02/23/2006 01:01:17 PM:

> Sorry - I am trying to cut back to just 2 pots of coffee per day and I 
the 
> lack of caffeine can make me a little fuzzy :-) Thank you for being 
> patient with me. 
> 
>  You have a working query, we just need to convert your INNER JOINs to 
> LEFT JOINs and move your join-specific WHERE conditions into the correct 

> ON clauses
> 
> SELECT p.id, p.prod_name, sum(oi.quantity) as qty
> FROM products as p
> INNER JOIN order_items as oi
> ON (p.id = oi.product_id)
> INNER JOIN orders as o
> ON (o.id = oi.order_id)
> AND o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
> GROUP BY p.id, p.prod_name
> 
> By placing a restriction in the WHERE clause, you are requiring a value 
> exist in that column after the JOINs are computed. That is why you have 
> been throwing out all unsold products before you even got to the GROUP 
BY 
> stage.  You cannot group on values that aren't going to be there so I 
> moved the two important columns of your SELECT statement back to the 
> products table (SELECT p.id, p.prod_name ...) and made sure that those 
> were the values you were grouping by.
> 
> Again, Thanks!
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 
> 
> Scott Haneda <lists@stripped> wrote on 02/23/2006 12:45:28 PM:
> 
> > > You're right. It was a dumb cut-and-paste mistake.
> > > 
> > > LEFT JOIN orders as o
> > >         on o.product_id = p.id
> > > 
> > > If fixing this doesn't give the correct results: What's missing? 
> What's
> > > incorrect? Please help us to help you.
> > 
> > Orders does not have a product_id column.
> > Let me see if I can explain this again, more better :-)
> > 
> > 
> > We have orders and order items, so for every orders, there are 1 or 
more
> > order items, pretty basic.  This SQL gets me almost what I want:
> > 
> > SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
> > FROM products as p
> > INNER JOIN order_items as oi
> > ON (p.id = oi.product_id)
> > INNER JOIN orders as o
> > ON (o.id = oi.order_id)
> > WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> > AND
> > (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
> > GROUP BY oi.product_id
> > 
> > However, there are 
> > mysql> select count(*) from products;
> > +----------+
> > | count(*) |
> > +----------+
> > |      109 |
> > +----------+
> > 1 row in set (0.00 sec)
> > 
> > So, 109 products in the products database, the first SQL above, will 
> give me
> > back a row for every order item that meets those criteria, however, it 

> does
> > not list products that were not ordered.
> > 
> > If I changed the first SQL to a date 10 years ago, I would get 0 rows, 
I
> > want 109 where the sum() is all 0.
> > 
> > Basically, my client is wanting to see what products are selling, and 
> which
> > ones are not, in a certain date range, and I need to add in the status 

> to
> > limit it to only certain orders.
> > 
> > Running these three SQL's does what I want, with a temp table, but I 
> find
> > the solution kinda strange, and know it can be done in one go:
> > 
> > CREATE TEMPORARY TABLE prod_report
> > SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
> > FROM products as p
> > INNER JOIN order_items as oi
> > ON (p.id = oi.product_id)
> > INNER JOIN orders as o
> > ON (o.id = oi.order_id)
> > WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> > AND
> > (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
> > GROUP BY oi.product_id
> > 
> > INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
> > 
> > SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
> > -- 
> > -------------------------------------------------------------
> > Scott Haneda                                Tel: 415.898.2602
> > <http://www.newgeo.com>                     Novato, CA U.S.A.
> > 
> > 

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