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
|
