missing operator in where not exists

[ycnan]

(I posted this as a reply, but should've started a new thread)

I have been trying to use the WHERE NOT EXISTS statement in a query, and I keep getting the error message that I'm missing an operator... I've read the article on subqueries, but am still not sure where I'm going wrong.

Here's my code:
SELECT S.name
FROM store as S
WHERE NOT EXISTS ((SELECT P.productID
FROM product as P)
EXCEPT
(SELECT R.productID
FROM store as S INNER JOIN (rentals as R LEFT JOIN product as P ON R.productID=M.productID) ON (R.storeID=S.storeID)
WHERE (P.supplier="Germany")))

What syntax am I missing?
Thanks.

 

[Jon K]

I don't think Access has the EXCEPT keyword in SQL Subquery.

Besides, Access doesn't optimize subqueries very well. You can use joins. Access does optimize joins well.

 

[The_Doc_Man]

I might have used the "WHERE field NOT IN (SELECT....)" construct rather than "NOT EXISTS" - and I've never seen the EXCEPT syntax for Access. Heck, even my old ORACLE 7 references don't list it other than as a qualifier for the ALTER USER or SET commands.

 

[ycnan]

Okay, the EXCEPT came from a class but apparently it's not used in Access. What would be the best way then to write a query to find a store that supplied at least one of each product in a table? (i.e. an example of division).

 

[Jon K]

What would be the best way then to write a query to find a store that supplied at least one of each product in a table?

The attached database contains four stores (S001 to S004) and five products (P001 to P005)
and a Rentals table:

StoreID		ProductID
S001		P001
S001		P002
S001		P003
S001		P003
S001		P004
S001		P005
S002		P001
S002		P002
S002		P003
S002		P004
S002		P005
S003		P001
S003		P002
S003		P004
S004		P001

The query "qry3 Stores that Supplied All Products" will return stores S001 and S002 as they each supplied all five products.

I'm not sure whether it is the best way to do it as I don't have much data to test its performance. Proper indexing of tables should help speed up performance if the tables are large.
.

 

[ycnan]

I thought that there might be a way to solve this problem doing one nested query using the WHERE NOT EXISTS or WHERE NOT IN statements, but I continue to get "missing operator" errors.

However, I did follow what you did in the 3 queries in the attached database and that definitely does get me the answer I want - thanks for your help!

 

[Jon K]

To do it in one query in my database, you can use two NOT IN's

SELECT [StoreID], [Name]
FROM Store
WHERE StoreID NOT IN (SELECT Store.StoreID FROM Product, Store WHERE Product.ProductID & " " & Store.StoreID NOT IN (SELECT ProductID & " " & StoreID FROM Rentals));


I inserted a space between ProductID and StoreID. This can avoid any possible compensating errors, particularly if the two fields contain only numeric digits e.g.

both "1234" & "5678" and "123" & "45678" will produce "12345678"
.

 

[ycnan]

Thanks - that works.

 

[May Parry22]

I tried many ways to use NOT EXISTS in a query, but they either
gave me a syntax error or just returned all the records.

Can I use NOT EXISTS instead of NOT IN? Is there any difference in the two?

I would appreciate any help. Thanks in advance.

 

[Jon K]

Can I use NOT EXISTS instead of NOT IN? Is there any difference in the two?

Not In and Not Exists function the same. The difference is in their syntaxes. For example, to find those customers that didn't place any orders in 2003, we can use Not In and Not Exists as follows:-

Select *
from tblCustomer
where CustID not in (Select CustID from tblOrder where year(OrderDate)=2003)

Select *
from tblCustomer
where not exists (Select * from tblOrder where year(OrderDate)=2003 and CustID=tblCustomer.CustID)

Many people find a statement with the IN operator easier to understand than one with the EXISTS operator. In the example, the subquery with Not Exists referenced the outer query by CustID=tblCustomer.CustID. It cannot be run as an independent query and is called a correlated subquery.

The EXISTS operator can properly be used only with a correlated subquery.


As another example, in my previous post I had this query:-

SELECT [StoreID], [Name]
FROM Store
WHERE StoreID NOT IN (SELECT Store.StoreID FROM Product, Store WHERE Product.ProductID & " " & Store.StoreID NOT IN (SELECT ProductID & " " & StoreID FROM Rentals));

if changed to Not Exists, it would have been:-

SELECT [StoreID], [Name]
FROM Store
WHERE NOT EXISTS (SELECT * FROM Product AS P, Store AS S WHERE NOT EXISTS (SELECT * FROM Rentals WHERE ProductID=P.ProductID AND StoreID=S.StoreID) AND S.StoreID=Store.StoreID);

Of course, I could have used one Not In and one Not Exists in the statement and still be able to get the same results.


Besides using Not In and Not Exists, we can also use Outer Joins (though it may sometimes require building more than one query) to obtain the same results.

Many people, including Pat, have pointed out that Access doesn't optimise subqueries well and have advised using joins instead.
.