From: Peter Brawley
Date: February 22 2006 4:15pm
Subject: Re: Inner join with left join
List-Archive: http://lists.mysql.com/mysql/195061
Message-Id: <43FC8E31.2050502@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 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 mycount;

PB

---

Scott Haneda wrote:
> 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 sum() for each, but
> only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost')
>
> This gets me pretty close, but does not mask out the orders that have the
> wrong status, as I do not know how to add in the join on orders
>
> SELECT p.prod_name, count(oi.product_id) as mycount
>        FROM products as p
>        LEFT JOIN order_items as oi
>        on (p.id = oi.product_id)
>        group by oi.product_id order by mycount;
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.0.0/266 - Release Date: 2/21/2006