How to view records that do not meet criteria

[Jason]

I have a table with two colums:
CustomerID and ProductName

The same customer may have just one product or multiple. I can do a query
and for the criteria, enter <> "Product X" and the results will show all the
customers that do not have Product X in the table. This may also include the
same patient that HAS received Product X, it just doesn't show it in the
results.
What I want to see, though, are customers that have NEVER received Product X
and even if they've received another product, I do NOT want that customer
listed.

Does anyone know how this can be accomplished?

Thanks

 

[Ken Sheridan]

You can restrict the results by means of a subquery. Lets assume you have a
table Customers and a table ProductsReceived, the latter being the one you
refer to in you post then the following will return the Customers who did not
receive Product X

SELECT *
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ProductsReceived
WHERE ProductsReceived.CustomerID = Customers.CustomerID
AND ProductName = "Product X");

The subquery will return rows where the CustomerID is the same as the outer
query's current CustomerID and the product is Product X, so by applying the
NOT EXISTS predicate to this only rows from the Customers table where no row
exists in the ProductsReceived table for that customer with "Product X" as
the ProductName.

You can of course join the ProductsReceived table, and Products table, which
I assume you also have, to the Customers table in the outer query if you want
to return data about which products the customers in question have received.

Ken Sheridan
Stafford, England

 

[Jason]

So if I want to see which customers did NOT receive product X and I have
customer #305 that has received product X and also product Y, with your
query below, won't customer #305 still show in the query with product Y
because he appears on a different row with a different product?
What I would like is for customer #305 to NOT show at all because he has
recived product X.

 

[Ken Sheridan]

No, customer #305 won't be returned if he has ever received product X,
regardless of what other products he has received. The query will only
return a row from the Customers table if the subquery returns no rows; this
is what the NOT EXISTS predicate means. So if customer #305 has received
product X his row won't be returned because the subquery will return at least
one row and consequently the NOT EXISTS predicate will be False. Whether
he's received any other products is irrelevant as the subquery concerns
itself only with product X. He may have been stuffed to the gills with a
dozen other products, he still won't be returned. Which is what I think you
were saying you wanted in you original post.

If customer #305 has never received product X on the other hand, the
subquery won't return any rows so the NOT EXISTS predicate will be True and
he will be returned. Again what other products he may or may not have
received are irrelevant.

Ken Sheridan
Stafford, England

 

[Jason]

I see what you're saying now.
Thanks for the advice, it seems to work well this way.

 

[Ken Sheridan]

It should do; it’s the standard solution to this sort of problem.

Ken Sheridan
Stafford, England