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

Hi Francisco

> 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)

But Christopher didn't refer to SQL Server, that's why I thought it
would be more relevant to show that this bug is non-existing in Jet SQL.

Further, I guess including the where clause will perform much slower?
If you really need to do that, couldn't this do (I have no SQL Server
to play with):

     au_id IN
     (SELECT au_id FROM Publishers WHERE Publishers.au_id Is Not Null)

/gustav


>>>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?