> You just need to invert a couple of things...
>
> 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)
> AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59"
> AND o.status not IN ('cancelled', 'pending', 'ghost')
> LEFT JOIN order_items as oi
> ON (o.id = oi.order_id)
> GROUP BY p.id
> ORDER by qty ASC
>
>
> That should give you a list of all products and a count of how many have been
> ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status of the
> order is neither 'cancelled', 'pending', or 'ghost'.
>
> The think to remember is that an ON clause can be as complex as a WHERE
> clause. The ON clause also determines which rows of which table participate in
> a JOIN. In this case the only table to be affected will be the one on the
> right side of a LEFT join (in an INNER join both tables are filtered). So you
> keep all of your products visible (as declared in the FROM clause) and
> optionally associate with each product an order and optionally past that to an
> order_item.
> HTH!
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
Not sure if this is related to my version of mysql, or something else?
--
-------------------------------------------------------------
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]
