Query customers who have never ordered specific product

[machumpion]

Hi,

I have a customer table (Name, Address), Product table (SKU, Type, Cost), and Orders Table (customer name, ProductSKU, Revenue, Date)

I'd like to query which customers have NOT ever ordered a specific product type (i.e. "Potato Chips"). How would I make this query? Thanks!

 

[pbaldy]

Create a query that returns those who HAVE ordered the product, then use the unmatched query wizard to compare that to the customer table.

 

[plog]

First, you should use an autonumber primary key in your customer table and not the name to link to orders. So, in my below code I will use CustomerID for that field.

Then you need a list of all Customer/Product permutations:

SELECT CustomerID, SKU FROM Customers, Products

Name that query 'UnorderedProducts_sub1'. Then to get the data you want, you link that query to Orders via a LEFT JOIN and exclude those in Orders:

SELECT UnorderedProducts_sub1.CustomerID, UnorderedProducts_sub1.SKU
FROM UnorderedProducts LEFT JOIN Orders on UnorderedProducts.CustomerID=Orders.CustomerID AND UnorderedProducts.SKU=Orders.SKU
WHERE Orders.SKU Is NULL

 

[plog]

Uh oh, just saw you want it by product type. My method still works, but it makes both queries a little more complex. That first query needs to list every Customer/Product Type instead of SKU. Do you have a table of unique Product types?

The second query would actually require a sub-query which would group up every order to its product type.

 

[machumpion]

Uh oh, just saw you want it by product type. My method still works, but it makes both queries a little more complex. That first query needs to list every Customer/Product Type instead of SKU. Do you have a table of unique Product types?

The second query would actually require a sub-query which would group up every order to its product type.

Hi plog,

I was able to query it successfully using pbaldy's method. I appreciate the time and effort you put in for putting together your method though. Thanks!

 

[pbaldy]

Glad it worked for you.