List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 23 2006 8:20pm
Subject:Re: Inner join with left join
View as plain text  
Scott,

If you Left Join to o and oi, and add 'OR oi.product_id IS NULL) to the 
WHere clause, I think you have it.

SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
LEFT JOIN order_items as oi
ON (p.id = oi.product_id)
LEFT 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" )
   OR oi.product_id IS NULL
GROUP BY oi.product_id

PB

-----
Scott Haneda wrote:
>> 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
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.0.0/267 - Release Date: 2/22/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