Re: Where clause question

[Eamon Daly]

Great explanation. By the way, Ed, what you might be looking
for is the HAVING clause, which culls records right before
the LIMIT is applied.
SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as 
discount
FROM `products`
HAVING discount > 10
LIMIT 10

Note that this is, by nature, /very/ slow: you're basically
selecting ALL records, then winnowing out records according
to the HAVING clause (with no optimization), then throwing
everything but the first 10 records away.
You can read more about it here:
http://dev.mysql.com/doc/mysql/en/SELECT.html
Search for HAVING.
____________________________________________________________
Eamon Daly

----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: "Ed Lazor" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 11, 2004 3:51 PM
Subject: Re: Where clause question


Because "discount" isn't one of:
1) a field on one of the tables your query is based on
2) a formula based on one or more of the fields from one or more of the
tables your query is based on.
What it is: an alias to the results of a function applied to 2 fields on
one of your tables.
Since the name "discount" is a reference to some of the *results* of this
particular query, it will be impossible for the WHERE clause to use the
RESULTS of a query to determine what rows should PARTICIPATE in the query
(WHERE clauses are evaluated BEFORE aliases are determined).
It's kind of like trying to drink from a glass before you fill it up.
Understand?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Ed Lazor" <[EMAIL PROTECTED]> wrote on 10/11/2004 04:33:27 PM:
I'm getting an unknown column error for discount with the following
query.
Any idea why?
-Ed
SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as
discount
FROM `products`
where discount > '10'
limit 10

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]