List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:February 23 2006 1:08am
Subject:Re: Inner join with left join
View as plain text  
> 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.


Thread
Inner join with left joinScott Haneda22 Feb
  • Re: Inner join with left joinSGreen22 Feb
  • MySQL Queries within OscommerceAM COMS22 Feb
    • Re: MySQL Queries within OscommerceSGreen22 Feb
  • Re: Inner join with left joinPeter Brawley22 Feb
    • Re: Inner join with left joinScott Haneda22 Feb
      • Re: Inner join with left joinSGreen22 Feb
        • Re: Inner join with left joinScott Haneda23 Feb
          • Re: Inner join with left joinJames Harvard23 Feb
            • Re: Inner join with left joinScott Haneda23 Feb
            • Re: Inner join with left joinSGreen23 Feb
              • Re: Inner join with left joinScott Haneda23 Feb
                • Re: Inner join with left joinSGreen23 Feb
                  • Re: Inner join with left joinSGreen23 Feb
                • Re: Inner join with left joinPeter Brawley23 Feb
      • Re: Inner join with left joinPeter Brawley25 Feb