List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 22 2006 4:15pm
Subject:Re: Inner join with left join
View as plain text  
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

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