> At 5:08 pm -0800 22/2/06, Scott Haneda wrote: >> I think we are close, thanks >> ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON >> conditions > >>> 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) > > Maybe this is where your problem is - you're joining to orders but > referencing order_items in your join condition. Shurely shome mishtake?*
I am not sure, but I think that is what I want. If it is of any help, I was able to do this with what I would call a hack, and some temp tables, the result is what I am after, however, I am not 100% happy with the method I used. // first make a selection of the data I want CREATE TEMPORARY TABLE prod_report SELECT p.id, oi.prod_name, sum(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 (o.id = oi.order_id) WHERE o.status NOT IN ('cancelled', 'pending', 'ghost') AND (o.created BETWEEN "2006-02-22 00:00:00" AND "2006-02-22 23:59:59") GROUP BY oi.product_id // select all products, set qty to '0', this fills in the gaps where there // are zero item products INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products) // re-seslect the real data, using group by to merge the duplicates // out of the select SELECT * FROM prod_report GROUP BY id ORDER BY prod_name -- ------------------------------------------------------------- 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]