')
AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59"
GROUP BY p.id, p.prod_name
(you can often speed up a join by moving conditions from the Where
clause to the unnamed side of an Inner or Left Join).
PB
-
Scott Haneda wrote:
Is this what you mean?
James Harvard [EMAIL PROTECTED] wrote on 02/22/2006
08:53:56 PM:
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
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
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
I hate remembering crap like this AFTER I hit send...
Because we want to limit our sum() to only those rows that match the ORDER
conditionals, we have to change our formula to recognized when to count
and when to not count an order_item.
SELECT p.id, p.prod_name, sum(if(o.id is
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 (
Scott Haneda [EMAIL PROTECTED] wrote on 02/22/2006 01:47:38 AM:
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
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
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;
Well,
ORDER by qty ASC
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 22, 2006 2:58 PM
To: MySql
Subject: [SPAM] - Re: Inner join with left join - Bayesian Filter
detected spam
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id
to be affected will be
the one on the right side of a LEFT join (in an INNER join both tables are
filtered). So you keep all of your products visible (as declared in the
FROM clause) and optionally associate with each product an order and
optionally past that to an order_item.
HTH!
Shawn Green
Database
', 'pending', or 'ghost'.
The think to remember is that an ON clause can be as complex as a WHERE
clause. The ON clause also determines which rows of which table participate in
a JOIN. In this case the only table to be affected will be the one on the
right side of a LEFT join (in an INNER join
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
Maybe this is
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
Maybe this
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
15 matches
Mail list logo
