Scott Haneda <[EMAIL PROTECTED]> wrote on 02/22/2006 03:58:10 PM:
> > Is this what you mean?
> >
> > SELECT
> > p.prod_name,
> > count(oi.product_id) AS mycount
> > FROM ORDERS AS o
> > INNER JOIN products ON o.id=p.id
> > LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
> > WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> > GROUP BY oi.product_id
> > ORDER BY mycount;
>
> Well, sort of, here is what I managed to coble together, which gets me
> pretty close, it is just what I want, other than it is missing products
with
> a zero count. This tells me those products have not been ordered ever,
but
> I would like to know what they are.
>
> SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
> FROM orders as o
> INNER JOIN order_items as oi
> ON (o.id = oi.order_id)
> LEFT JOIN products as p
> ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND
> (o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
> GROUP BY oi.product_id
> ORDER by qty ASC
>
>
You just need to invert a couple of things...
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22
23:59:59"
AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC
That should give you a list of all products and a count of how many have
been ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status
of the order is neither 'cancelled', 'pending', or 'ghost'.
The think to remember is that an ON clause can be as complex as a WHERE
clause. The ON clause also determines which rows of which table
participate in a JOIN. In this case the only table to be affected will be
the one on the right side of a LEFT join (in an INNER join both tables are
filtered). So you keep all of your products visible (as declared in the
FROM clause) and optionally associate with each product an order and
optionally past that to an order_item.
HTH!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
