|
Home > Archive > MySQL ODBC Connector > February 2006 > Inner join with left join
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Inner join with left join
|
|
| Scott Haneda 2006-02-25, 9:43 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 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Scott Haneda 2006-02-25, 9:43 am |
| > You just need to invert a couple of things...
>
> 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)
> AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59"
> AND o.status not IN ('cancelled', 'pending', 'ghost')
> LEFT JOIN order_items as oi
> ON (o.id = oi.order_id)
> GROUP BY p.id
> ORDER by qty ASC
>
>
> That should give you a list of all products and a count of how many have been
> ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status of the
> order is neither 'cancelled', '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 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!
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
Not sure if this is related to my version of mysql, or something else?
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| Scott Haneda 2006-02-25, 9:43 am |
| > At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
>
>
> Maybe this is where your problem is - you're joining to orders but
> referencing order_items in your join condition. Shurely shome mishtake?*
I am not sure, but I think that is what I want. If it is of any help, I was
able to do this with what I would call a hack, and some temp tables, the
result is what I am after, however, I am not 100% happy with the method I
used.
// first make a selection of the data I want
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-22 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
// select all products, set qty to '0', this fills in the gaps where there
// are zero item products
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
// re-seslect the real data, using group by to merge the duplicates
// out of the select
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2006-02-25, 9:43 am |
| --=_alternative 00512DB68525711E_=
Content-Type: text/plain; charset="US-ASCII"
James Harvard <james.lists.tech@harvard-digital.co.uk> wrote on 02/22/2006
08:53:56 PM:
> At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
>
>
> Maybe this is where your problem is - you're joining to orders but
> referencing order_items in your join condition. Shurely shome mishtake?*
>
> "2006-02-22 23:59:59"
>
> * ask a Brit, or consult http://en.wikipedia.
> org/wiki/ Private_Eye#Examples
_of_humour
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.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00512DB68525711E_=--
| |
| Scott Haneda 2006-02-25, 9:43 am |
| > 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2006-02-25, 9:43 am |
| --=_alternative 006280F58525711E_=
Content-Type: text/plain; charset="US-ASCII"
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@newgeo.com> wrote on 02/23/2006 12:45:28 PM:
What's[color=darkred
]
>
> 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.
>
>
--=_alternative 006280F58525711E_=--
| |
| SGreen@unimin.com 2006-02-25, 9:43 am |
| --=_alternative 00632FAE8525711E_=
Content-Type: text/plain; charset="US-ASCII"
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 null,0,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
I think I need a nap! --- SORRY!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
SGreen@unimin.com wrote on 02/23/2006 01:01:17 PM:
> 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[color=darkred]
> 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@newgeo.com> wrote on 02/23/2006 12:45:28 PM:
>
> What's
more[color=darkred]
> give me
[color=darkred]
> does
I[color=darkred]
> which
[color=darkred]
> to
> find
--=_alternative 00632FAE8525711E_=--
| |
| Peter Brawley 2006-02-25, 9:43 am |
| --=======AVGMAIL-43FE18F61A94=======
Content-Type: multipart/alternative; boundary=------------ 03090403040109060608
0900
-------------- 03090403040109060608
0900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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:
>
> 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
>
-------------- 03090403040109060608
0900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Scott,<br>
<br>
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.<br>
<br>
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty<br>
FROM products as p<br>
LEFT JOIN order_items as oi<br>
ON (p.id = oi.product_id)<br>
LEFT JOIN orders as o<br>
ON (o.id = oi.order_id)<br>
WHERE ( o.status NOT IN ('cancelled', 'pending', 'ghost')<br>
AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23
23:59:59" )<br>
OR oi.product_id IS NULL<br>
GROUP BY oi.product_id<br>
<br>
PB<br>
<br>
-----<br>
Scott Haneda wrote:
<blockquote cite="midC02334B8. 3A49A%25lists@newgeo
.com" type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
</body>
</html>
-------------- 03090403040109060608
0900--
--=======AVGMAIL-43FE18F61A94=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-43FE18F61A94=======
Content-Type: text/plain; charset=us-ascii
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-43FE18F61A94=======--
| |
| Peter Brawley 2006-02-25, 9:44 am |
| --=======AVGMAIL-43FFC6D50CCA=======
Content-Type: multipart/alternative; boundary=------------ 08060009040004050809
0501
-------------- 08060009040004050809
0501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Scott,
I think Shawn nailed it with
SELECT
p.id, p.prod_name,
SUM(IF(o.id IS NULL,0,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 (oi.order_id = o.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
(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:
>
> Well, sort of, here is what I managed to coble together, which gets me
> pretty close, it is just what I want, other than it is missing products with
> a zero count. This tells me those products have not been ordered ever, but
> I would like to know what they are.
>
> SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
> FROM orders as o
> INNER JOIN order_items as oi
> ON (o.id = oi.order_id)
> LEFT JOIN products as p
> ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND
> (o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
> GROUP BY oi.product_id
> ORDER by qty ASC
>
>
>
-------------- 08060009040004050809
0501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Scott,<br>
<br>
I think Shawn nailed it with<br>
<pre>SELECT
p.id, p.prod_name,
SUM(IF(o.id IS NULL,0,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 (oi.order_id = o.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</pre>
(you can often speed up a join by moving conditions from the Where
clause to the unnamed side of an Inner or Left Join).<br>
<br>
PB<br>
<br>
-----<br>
<br>
Scott Haneda wrote:
<blockquote cite="midC0221062. 3A389%25lists@newgeo
.com" type="cite">
<blockquote type="cite">
<pre wrap="">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;
</pre>
</blockquote>
<pre wrap=""><!---->
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products with
a zero count. This tells me those products have not been ordered ever, but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC
</pre>
</blockquote>
</body>
</html>
-------------- 08060009040004050809
0501--
--=======AVGMAIL-43FFC6D50CCA=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--=======AVGMAIL-43FFC6D50CCA=======
Content-Type: text/plain; charset=us-ascii
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-43FFC6D50CCA=======--
|
|
|
|
|