[AccessD] Query fails when using NOT IN ???

Gustav,
   David quoted an article for Sql Server in which this is 100% 
reproducable.  I think he posted it here because of the initial posts of 
problems where the IN clause or rather (NOT IN) was not behaving as 
expected.  for that manner I've also seen it do this, one major 
workaround is to explicitly point the inner quiery to that of the outer 
query. that way any possible errors will be trapped

SUCH as

  DELETE FROM
    TitleAuthor
  WHERE
    au_id IN (SELECT au_id FROM Publishers WHERE Publishers.au_id=TitleAuthor.au_id)


Gustav Brock said the following on 4/22/2004 12:00 AM:

>>Pinnacle's SQL Server Professional, February 2002 (Volume 8, Number 2),
>>"Known Problems with IN/NOT IN()"
>>    
>>
>I can't recognize this; it behaves very politely.
>The syntax doesn't work in Jet SQL, but this does:
>
>  DELETE FROM
>    TitleAuthor
>  WHERE
>    au_id IN (SELECT au_id FROM Publishers)
>
>and it deletes zero records if Publishers is empty. If au_id doesn't
>exist as a field, Access asks for it as a parameter.
>So this is exactly as expected.
>
>Can you provide another example? Or are you talking about another SQL
>engine?
>
>/gustav
> 
>  
>
-- 
-Francisco