Inner join with left join

[Scott Haneda]

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: [phone]
<http://www.newgeo.com>                     Novato, CA U.S.A.