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

Hi David

> Pinnacle's SQL Server Professional, February 2002 (Volume 8, Number 2),
> "Known Problems with IN/NOT IN()"

So which are those known problems?
Susan described one only.
 
> This may only apply to SQL Server and not Access, but it has bit me in 
> the a$$ before. The use of IN and NOT IN for a value list is still ok to 
> use, but not for subqueries.
 
> I too, used it many times in Access, but then again, I also used the MAX 
> statement through the QBE window, assuming that I was using it 
> correctly, only to find out as I started using QA that I had been 
> writing Max Statements incorrectly...but I digress...:S

How is MAX used incorrectly? I've never met any problems with MAX (not
that I know of).
 
> If you get your hands on that issue, also check out "The Rogue Delete":
 
> BEGIN TRANSACTION T1
> SELECT COUNT (*) FROM TitleAuthor
> DELETE TitleAuthor WHERE au_id IN ( SELECT au_id FROM Publishers)
> SELECT COUNT (*) FROM TitleAuthor
> ROLLBACK T1
 
> This deletes all rows in the TitleAuthor table, even though there is no 
> au_id on the Publishers table :)

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
 
> Christopher Hawkins wrote:
 
>>A known issue?  No kidding.  I've been using the "IN (SELECT..."
>>statement for years and have never had a problem.
>>
>>Do you know of any MSDN or similar documentation of this issue?
>>
>>-Christopher-