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
[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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.
> >
> >
