Scott, I think Shawn nailed it with 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 (oi.order_id = o.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(you can often speed up a join by moving conditions from the Where clause to the unnamed side of an Inner or Left Join). PB ----- Scott Haneda wrote: 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 |
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]