how to use Exists function in ms access

Question

how to use Exists function in ms access

Ir4Qe DesiGner 1

hi guys. i have database ms access 2010
i have 3 tables

i need search who customer not have materials selected in table orders
example:

i select materials:

Stereo
Lamp

i need get who customer not have this material in table orders
the result should be:

Customers:
2 beghy
[4] john

DOWNLOAD DATABASE:

https://hostr.co/qnqzGkWdY1aP

1 answer

Your answer

Answer 1

DBG 2,381 Volunteer Moderator

Hi. Not sure if this is correct, but here goes...

SELECT T1.Id, CustomerName
FROM tbl_customers T1
LEFT JOIN tbl_orders T2
ON T1.Id=T2.CustomerId
WHERE NOT EXISTS(SELECT * FROM tbl_orders S1
WHERE S1.CustomerId=T1.Id AND S1.MaterialId IN(SELECT Id FROM tbl_materials WHERE Selected=True))

Ir4Qe DesiGner 1

hmmm no bro, wrong.
i get the solution

SELECT * FROM tblCustomers WHERE NOT ID IN (SELECT CustomerID FROM tblOrders WHERE MaterialID IN (SELECT Id FROM tblMaterials WHERE Selected = -1));

Ken Sheridan 2,851 Reputation points

2021-10-17T11:57:40.847+00:00

Two points here:

The NOT IN operator is not always reliable because it does not cater for NULLs. You can see this by expanding the expression algebraically, e.g.

a NOT IN (x,y,NULL)

expands to:

(a<>x) AND (a<>y) AND (a <>NULL)

which evaluates to NULL of course, neither TRUE nor FALSE. because nothing can be equal to or not equal to NULL, even another NULL.

Two subqueries are unnecessary. The NOT EXISTS predicate can be applied to a single subquery which joins the orders and materials tables:

SELECT *
FROM tbl_Customers
WHERE NOT EXISTS
(SELECT *
FROM tbl_orders INNER JOIN tbl_Materials
ON tbl_orders.MaterialId = tbl_Materials.Id
WHERE Selected = TRUE
AND tbl_orders.CustomerId = tbl_Customers.ID);

Share via

how to use Exists function in ms access

1 answer

Your answer