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
