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


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