-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 16 August 2005 15:04 To: Andrew Hargreaves Cc: mysql@lists.mysql.com Subject: Re: Why does this fail
"Andrew Hargreaves" <[EMAIL PROTECTED]> wrote on 08/16/2005 09:13:56 AM: > Can anyone explain why this SQL statement would fail? A friend was > asked it > in a pre-interview assessment and it threw him a bit. > > SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from > PstlAddr T1 > Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from > PrsnAddress T2) > > Several people have already responded with the comma vs. alias (T1.BldgName...). Moving on... I believe that if there is a single record in PrsnAddress, the whole query will return nothing. That is because the inner query of the EXISTS clause is not linked to any term from the outer query (as others have mentioned). However, the converse should be true that if PrsnAddress is empty, then you will get the results you expect (WHERE T1.City='London'...) because the NOT EXISTS would evaluate as TRUE. This is a good example why all developers should test for both positive and negative results from their queries. If someone had only tested with an empty PrsnAddress table, they could have been tricked into thinking the query operated correctly. Thanks for all your replies. The typos were my fault as I was in a bit of a rush and didn't check the syntax properly. I think the main problem relates to the correlation of the sub qurty as was mentioned previously. An interesting question nonetheless. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
