On Mon, 2004-02-16 at 09:07, Batara Kesuma wrote: > Hi, > > 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?
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 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
