If you want to see all of the products {even those that have never been
ordered} then you need to SELECT ... FROM products ... LEFT JOIN orders
I think you also have to do a LEFT JOIN on order_items
And pull prod_name from products {don't know what the column name in
products is}.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
LEFT JOIN order_items as oi
ON (p.id = oi.product_id)
LEFT JOIN orders as o
ON (o.id = oi.order_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 p.id
ORDER by qty ASC
-----Original Message-----
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 22, 2006 2:58 PM
To: MySql
Subject: [SPAM] - Re: Inner join with left join - Bayesian Filter
detected spam
> 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
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
