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
