Batara Kesuma <[EMAIL PROTECTED]> 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
