NYCPHP Meetup

[Michael Sims]

On Tuesday 21 March 2006 11:28, Stephen Musgrave wrote:

> The almighty list:
>
> I am struggling with including a field returned from the subselect in
> the WHERE clause of the parent SELECT.  I keep on getting an error
> that the field is unknown:
>
> "Unknown column 'pt_prgm_term_overall_end_date' in 'where clause'"
>
> I have seen that this is a bug in previous versions of MySQL, but I
> am using 4.1.18 and it was to have been fixed by this version, 4.1.16
> I believe.

Can't do it.  No aliases in WHERE statement.  Put it in HAVING instead, and 
it will work.

http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html

Think of it this way: when it does the WHERE, it's trying to figure out 
what rows should be included in the results - a first stab at the problem.  
It hasn't even looked at the SELECT part of the statement yet - once it 
figures out what rows should be included overall, then it'll take a look 
at the SELECT fields to decide which columns from those rows it needs.  
And THEN, only then, will it look at the HAVING section - a last stab at 
winnowing the result set.

So: if you want to eliminate unwanted results early (to make queries 
quicker), put the condition early in the WHERE clause.  If you want to 
eliminate unwanted results late (because you're doing something tricksy), 
put it in the HAVING clause.


Michael Sims