Re: Inner join with left join

2006-02-24 Thread Peter Brawley
Scott, I think Shawn nailed it with SELECT p.id, p.prod_name, SUM(IF(o.id IS NULL,0,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 (oi.order_id = o.id) AND o.status NOT IN ('cancelled', 'pending', 'ghost') AND

Re: Inner join with left join

2006-02-23 Thread Peter Brawley
Scott, If you Left Join to o and oi, and add 'OR oi.product_id IS NULL) to the WHere clause, I think you have it. SELECT p.id, oi.prod_name, sum(oi.quantity) as qty FROM products as p LEFT JOIN order_items as oi ON (p.id = oi.product_id) LEFT JOIN orders as o ON (o.id = oi.order_id) WHERE ( o

Re: Inner join with left join

2006-02-23 Thread SGreen
I hate remembering crap like this AFTER I hit send... Because we want to limit our sum() to only those rows that match the ORDER conditionals, we have to change our formula to recognized when to count and when to not count an order_item. SELECT p.id, p.prod_name, sum(if(o.id is null,0,oi.quanti

Re: Inner join with left join

2006-02-23 Thread SGreen
Sorry - I am trying to cut back to just 2 pots of coffee per day and I the lack of caffeine can make me a little fuzzy :-) Thank you for being patient with me. You have a working query, we just need to convert your INNER JOINs to LEFT JOINs and move your join-specific WHERE conditions into th

Re: Inner join with left join

2006-02-23 Thread Scott Haneda
> 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. Orders does not have a product_id column. Let me see if I can expla

Re: Inner join with left join

2006-02-23 Thread SGreen
James Harvard <[EMAIL PROTECTED]> 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 > >> FR

Re: Inner join with left join

2006-02-22 Thread Scott Haneda
> 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.produc

Re: Inner join with left join

2006-02-22 Thread James Harvard
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

Re: Inner join with left join

2006-02-22 Thread Scott Haneda
> 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 ('cance

Re: Inner join with left join

2006-02-22 Thread SGreen
Scott Haneda <[EMAIL PROTECTED]> wrote on 02/22/2006 03:58:10 PM: > > Is this what you mean? > > > > SELECT > > p.prod_name, > > count(oi.product_id) AS mycount > > FROM ORDERS AS o > > INNER JOIN products ON o.id=p.id > > LEFT JOIN order_items AS oi ON (p.id = oi.product_id) > > WHERE o.stat

RE: [SPAM] - Re: Inner join with left join - Bayesian Filter detected spam

2006-02-22 Thread Gordon Bruce
:00" AND "2006-02-22 23:59:59") GROUP BY p.id ORDER by qty ASC -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 22, 2006 2:58 PM To: MySql Subject: [SPAM] - Re: Inner join with left join - Bayesian Filter detected spam > Is

Re: Inner join with left join

2006-02-22 Thread Scott Haneda
> Is this what you mean? > > SELECT > p.prod_name, > count(oi.product_id) AS mycount > FROM ORDERS AS o > INNER JOIN products ON o.id=p.id > LEFT JOIN order_items AS oi ON (p.id = oi.product_id) > WHERE o.status NOT IN ('cancelled', 'pending', 'ghost') > GROUP BY oi.product_id > ORDER BY mycou

Re: Inner join with left join

2006-02-22 Thread Peter Brawley
Scott, I need a report that shows me all the products with a sum() for each, but only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost') Is this what you mean? SELECT p.prod_name, count(oi.product_id) AS mycount FROM ORDERS AS o INNER JOIN products ON o.id=p.id LEFT JOIN

Re: Inner join with left join

2006-02-22 Thread SGreen
Scott Haneda <[EMAIL PROTECTED]> wrote on 02/22/2006 01:47:38 AM: > Got myself a little stumped here, 4.0.18-standard > > Three tables in this mess, orders, order_items and products. > > orders.prod_id = order_items.prod_id = products.prod_id is how I relate them > all to each other. > > orde

Inner join with left join

2006-02-21 Thread Scott Haneda
Got myself a little stumped here, 4.0.18-standard Three tables in this mess, orders, order_items and products. orders.prod_id = order_items.prod_id = products.prod_id is how I relate them all to each other. order_items has a quantity field. I need a report that shows me all the products with a