Query to find products that have been purchased multiple times

Databases
1

Hi. I’m trying to write a query with a list of part numbers and I want to return customers who have ordered more than one of the individual products that are in that list. To be specific, the customer should not be included if they ordered one of each of products on that list, only two of the same exact product contained in that list. Here are the structures of my tables:

Orders
CustomerID
BillingPhoneNumber
OrderID

OrderDetails - there is one record for every item that has been purchased, so one or more of these per order.
OrderID
ProductCode
ProductName
Quanity

As an example, I want to return a list of customerIDs and BillingPhoneNumbers for customers who purchased more than either of ProductCodes widget1, widget3, and widget5.

This is beyond my basic knowledge of SQL. Thanks for any help you can give. I’ll pay it forward.

3

Have you tried anything? Which database are you using?

This is a simple inner join so it would be great if you could show us what you’ve done. For sure, you simply did a little mistake which a second pair of eyes will be able to spot :slight_smile:

4

SELECT DISTINCT Orders.CustomerID , Orders.BillingPhoneNumber FROM Orders INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID AND OrderDetails.ProductCode IN ('widget1','widget3','widget5') AND OrderDetails.Quanity > 1

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.