List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 23 2006 6:01pm
Subject:Re: Inner join with left join
View as plain text  
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 the correct 
ON clauses

SELECT p.id, p.prod_name, sum(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 (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
GROUP BY p.id, p.prod_name

By placing a restriction in the WHERE clause, you are requiring a value 
exist in that column after the JOINs are computed. That is why you have 
been throwing out all unsold products before you even got to the GROUP BY 
stage.  You cannot group on values that aren't going to be there so I 
moved the two important columns of your SELECT statement back to the 
products table (SELECT p.id, p.prod_name ...) and made sure that those 
were the values you were grouping by.

Again, Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Scott Haneda <lists@stripped> wrote on 02/23/2006 12:45:28 PM:

> > 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 explain this again, more better :-)
> 
> 
> We have orders and order items, so for every orders, there are 1 or more
> order items, pretty basic.  This SQL gets me almost what I want:
> 
> SELECT p.id, oi.prod_name, sum(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 (o.id = oi.order_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND
> (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
> GROUP BY oi.product_id
> 
> However, there are 
> mysql> select count(*) from products;
> +----------+
> | count(*) |
> +----------+
> |      109 |
> +----------+
> 1 row in set (0.00 sec)
> 
> So, 109 products in the products database, the first SQL above, will 
give me
> back a row for every order item that meets those criteria, however, it 
does
> not list products that were not ordered.
> 
> If I changed the first SQL to a date 10 years ago, I would get 0 rows, I
> want 109 where the sum() is all 0.
> 
> Basically, my client is wanting to see what products are selling, and 
which
> ones are not, in a certain date range, and I need to add in the status 
to
> limit it to only certain orders.
> 
> Running these three SQL's does what I want, with a temp table, but I 
find
> the solution kinda strange, and know it can be done in one go:
> 
> CREATE TEMPORARY TABLE prod_report
> SELECT p.id, oi.prod_name, sum(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 (o.id = oi.order_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND
> (o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
> GROUP BY oi.product_id
> 
> INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
> 
> SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
> -- 
> -------------------------------------------------------------
> 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