Hi Diana, > > 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? > > > Conditions with fields from the table in the LEFT JOIN side (in this > case, "network") should be in the ON clause, not in the WHERE clause. > Check the manual about using LEFT JOIN and try: > > 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 > AND network.level <= member.photo_level) > ORDER BY member.last_login DESC > LIMIT 0,3
Thank you for the reply. I have it fixed, the problem is I should have used "HAVING" instead of "WHERE" and I couldn't use "AS level", because HAVING will confuse it with network.level which is NULL. So here is the working query. SELECT IF(ISNULL(network.level), 4, network.level) AS level_alias, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) HAVING level_alias <= member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Regards, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
