Using NOT together with EXIST

  • Thread starter Thread starter Frank M.
  • Start date Start date
F

Frank M.

For simplicity let's assume that I have a table Cust with
fields CustID and CustName, another table CustSpecial
with field CustID. The two table are related through
CustID key.

Now, I can get the following query to work:

SELECT * FROM Cust
WHERE EXIST (SELECT * FROM CustSpecial WHERE
CustSpecial.CustID = Cust.CustID)

It gives me the Customers that are also present in the
CustSpecial table. However, if I insert a "NOT" between
WHERE and EXIST, it gives me nothing. I would expect
those customers that are not present in CustSpecial.

What am I doing wrong here?



Regards,

Frank M.
 
The subquery works fine here.
I did specify a particular field in the subquery:

SELECT Cust.*
FROM Cust
WHERE NOT EXISTS
(SELECT CustSpecial.[YourPrimaryKeyFieldHere]
FROM CustSpecial
WHERE CustSpecial.CustID = Cust.CustID);

(For this simplified example, it may be more efficient to use an outer join
query where the foreign key is null.)
 
Changing the reserved word from "EXIST" to "EXISTS" makes
it work. Strange though, if you omit the word NOT the
query works both with "EXIST" and "EXISTS".

Thanks for the tip about using the outer join.


With kind regards,

Frank M.
-----Original Message-----
The subquery works fine here.
I did specify a particular field in the subquery:

SELECT Cust.*
FROM Cust
WHERE NOT EXISTS
(SELECT CustSpecial.[YourPrimaryKeyFieldHere]
FROM CustSpecial
WHERE CustSpecial.CustID = Cust.CustID);

(For this simplified example, it may be more efficient to use an outer join
query where the foreign key is null.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Frank M. said:
For simplicity let's assume that I have a table Cust with
fields CustID and CustName, another table CustSpecial
with field CustID. The two table are related through
CustID key.

Now, I can get the following query to work:

SELECT * FROM Cust
WHERE EXIST (SELECT * FROM CustSpecial WHERE
CustSpecial.CustID = Cust.CustID)

It gives me the Customers that are also present in the
CustSpecial table. However, if I insert a "NOT" between
WHERE and EXIST, it gives me nothing. I would expect
those customers that are not present in CustSpecial.

What am I doing wrong here?



Regards,

Frank M.
Click to expand...


.
Click to expand...
 
Back
Top