Posted by Erland Sommarskog on 04/27/06 00:38

David (david.goodyear@gmail.com) writes:
> Is there a NOT operator in sql? I actually want NOT EXISTS as i believe
> these two statements would get a complete data set. One gets the
> records which also have a recording match, and then i want to get all
> without. After this i was going to union the two sets together.

Yes, NOT EXISTS is a common operation. This query returns the two
customers in the Northwind database that does not have any orders:

SELECT C.CustomerID, C.CompanyName
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)


I believe the Northwind database is available in Access as well. The
syntax should work in Access as well.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: sql query help
• Prev in forum: Re: Need help with SQL satement - newbie
• Thread view: Re: Please help quick statement check

[Reply to this message]