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.
>
>
