List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:February 22 2006 6:47am
Subject:Inner join with left join
View as plain text  
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;
-- 
-------------------------------------------------------------
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