On Wednesday 22 January 2003 20:28, gordon at hammy dot lonestar dot org wrote:
> >Description: > > The following silly query (silly because it should return no > records since it is impossible for a.type to have two different > values simultaneously), returns two records when it should > return none with the enclosed test data. > > select a.*, b.* from dns_rec a left join dns_rec b > on (a.zone = b.zone and > a.rightside = b.rightside and > a.type = 'A' and b.type = 'A' and > a.leftside = '' and b.leftside != '') > where b.zone is null and a.type = 'MX' > ; This happens because you include filtering condition like a.type='A' in ON clause. This is old and known behaviour. If you move out these conditions from ON part to the WHERE, you get correct result: mysql> select a.*, b.* from dns_rec a left join dns_rec b on (a.zone = b.zone and a.rightside = b.rightside) where a.type = 'A' and b.type = 'A' and a.leftside = '' and b.leftside != '' and b.zone is null and a.type = 'MX'; Empty set (0.00 sec) -- 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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
