Got myself a little stumped here, 4.0.18-standard
Three tables in this mess, orders, order_items and products.
orders.prod_id = order_items.prod_id = products.prod_id is how I relate them
all to each other.
order_items has a quantity field.
I need a report that shows me all the products with a sum() for each, but
only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost')
This gets me pretty close, but does not mask out the orders that have the
wrong status, as I do not know how to add in the join on orders
SELECT p.prod_name, count(oi.product_id) as mycount
FROM products as p
LEFT JOIN order_items as oi
on (p.id = oi.product_id)
group by oi.product_id order by mycount;
--
-------------------------------------------------------------
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]
