List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 23 2006 2:52pm
Subject:Re: Inner join with left join
View as plain text  
James Harvard <james.lists.tech@stripped> wrote on 02/22/2006 
08:53:56 PM:

> 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?*
> 
> > >         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
> 
> * ask a Brit, or consult http://en.wikipedia.
> org/wiki/Private_Eye#Examples_of_humour

You're right. It was a dumb cut-and-paste mistake.

LEFT JOIN orders as o
        on o.product_id = p.id

If fixing this doesn't give the correct results: What's missing? What's 
incorrect? Please help us to help you.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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