Using NOT IN in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use NOT IN in a query? I have an initial query generated
by form input. I also give the user a chance to include items from another
query. I use the UNION statement to join the two. When the results are such
that both the form generated SQL Statement and the unioned query can return
the same data, I do not want it to show up twice. I was using NOT IN with
the subquery being the SQL of the unioned query.

Can I do this? Do I need to put the NOT IN clause (and subquery) in the
unioned SQL?

Thanks
 
You shouldn't be getting any duplicates in a union query anyway. A union
query by default returns only distinct records. You have to explicitly use
the ALL keyword to get it to return duplicate records.

But yes, you can use NOT IN in a query. This query ...

SELECT ProductID FROM Products WHERE ProductID < 10
UNION SELECT ALL ProductID FROM Products WHERE ProductID NOT IN (SELECT
ProductID FROM Products WHERE ProductID < 10)

Returns exactly the same records as this query ...

SELECT ProductID FROM Products WHERE ProductID < 10
UNION SELECT ProductID FROM Products

The ProductID fields with values < 10 are not returned twice in the second
query, because I took out the ALL keyword.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
When the results are such
that both the form generated SQL Statement and the unioned query can return
the same data, I do not want it to show up twice.
Click to expand...

They won't. The UNION operator removes all duplicates (unless you use
the UNION ALL variant).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top