List:General Discussion« Previous MessageNext Message »
From:Victoria Reznichenko Date:February 16 2004 10:15am
Subject:Re: Strange behavior with IF?
View as plain text  
Batara Kesuma <bkesuma@stripped> wrote:
> 
> Can someone tell me why this query doesn't work?
> 
> SELECT IF(ISNULL(network.level), 4, network.level) AS level,
> member.photo_level 
> FROM member 
> LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) 
> ORDER BY member.last_login DESC 
> LIMIT 0,3
> 
> +-------+-------------+
> | level | photo_level |
> +-------+-------------+
> |     4 |           4 |
> |     4 |           4 |
> |     4 |           4 |
> +-------+-------------+
> 3 rows in set (0.01 sec)
> 
> Then, when I add WHERE.
> 
> SELECT IF(ISNULL(network.level), 4, network.level) AS level,
> member.photo_level 
> FROM member 
> LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) 
> WHERE level <= member.photo_level
> ORDER BY member.last_login DESC 
> LIMIT 0,3
> 
> Empty set (0.00 sec)
> 
> I think it is supposed to return all rows, since all level is the same
> as photo_level, but why does it return empty set? 

You can't refer to the column alias in the WHERE clause. Use HAVING clause instead.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com




Thread
Strange behavior with IF?Batara Kesuma16 Feb
  • Re: Strange behavior with IF?Victoria Reznichenko16 Feb
  • Re: Strange behavior with IF?Diana Soares16 Feb
    • Re: Strange behavior with IF?Batara Kesuma16 Feb