List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 11 2002 4:32am
Subject:Re: Problems with alias names in WHERE clause
View as plain text  
At 20:57 -0700 9/10/02, Nicholas Berry wrote:
>I search high and low for an explanation of this at
>http://www.mysql.com/doc (hoping I didn't overlook anything) thought it
>might be easier to ask this list to see if I'm the only one with this
>problem.
>
>With the query statement -
>   SELECT i.id image_id, ifnull(i.photographer,"6") photo_id,
>ifnull(i.owner,"6") owner_id,
>	concat(cp1.fname,' ',cp1.lname) photo_name,
>	concat(cp2.fname,' ',cp2.lname) owner_name,
>	FROM image I, common.persons cp1, common.persons cp2
>	WHERE id='8200' AND photo_id=cp1.id AND owner_id=cp2.id
>
>- an error is reported "ERROR 1054: Unknown column 'photo_id' in 'where
>claus'".  This query is failing at the reference to the column alias in
>the WHERE claus.  Is this right? Should it be acting this way?  If I
>replace photo_id in the WHERE claus with 'ifnull(i.photographer,"6")'
>then the correct result is produced.
>
>Can someone please help me understand what I'm missing and possibly why
>this approach does not work.

You can't refer to aliases in WHERE clauses.  Aliases refer to columns
that have been selected, WHERE clauses determine which columns should
be selected.  In essence, WHERE refers to "input" columns, which occurs
earlier than the aliases (which refer to "output" columns).

>
>Thanks,
>
>Nicholas Berry
>Systems Engineer
>RagingWire Telecommunications, Inc.
>916.286.4048 ph
>916.921.4148 fax
>nberry@stripped
>www.ragingwire.com

Thread
Problems with alias names in WHERE clauseNicholas Berry11 Sep
Re: Problems with alias names in WHERE clausePaul DuBois11 Sep
  • Re: Problems with alias names in WHERE clauseZak Greant11 Sep
    • Re: Problems with alias names in WHERE clauseZak Greant13 Sep