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
>With the query statement -
> SELECT i.id image_id, ifnull(i.photographer,"6") photo_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).
>RagingWire Telecommunications, Inc.