Find Customers Who Have Ordered Every Product Query Using MS SQL Server?

To find customers who have ordered every product, you need to ensure that each customer has ordered all products available in your inventory. This involves comparing the list of products ordered by each customer with the full list of products.

Assuming you have the following tables:

• Products:

  • ProductID
  • ProductName

• OrderDetails:

  • OrderID
  • ProductID
  • Quantity

• Orders:

  • OrderID
  • CustomerID

Here’s how you can write the SQL query:

SQL Query

WITH ProductCount AS (

    SELECT COUNT(DISTINCT ProductID) AS TotalProducts

    FROM Products

),

CustomerProductCount AS (

    SELECT

        o.CustomerID,

        COUNT(DISTINCT od.ProductID) AS OrderedProducts

    FROM Orders o

    JOIN OrderDetails od ON o.OrderID = od.OrderID

    GROUP BY o.CustomerID

),

CustomersWithAllProducts AS (

    SELECT

        c.CustomerID

    FROM CustomerProductCount c

    JOIN ProductCount p

        ON c.OrderedProducts = p.TotalProducts

)

SELECT

    c.CustomerID

FROM CustomersWithAllProducts c;

Explanation:

1. CTE ProductCount:

   • COUNT(DISTINCT ProductID) AS TotalProducts: Calculates the total number of distinct products available.

2. CTE CustomerProductCount:

   • COUNT(DISTINCT od.ProductID) AS OrderedProducts: Counts the number of distinct products ordered by each customer.
   • GROUP BY o.CustomerID: Groups the results by CustomerID to get the count of distinct products ordered by each customer.

3. CTE CustomersWithAllProducts:

   • JOIN ProductCount p ON c.OrderedProducts = p.TotalProducts: Filters customers whose count of ordered products matches the total number of products available.

4. Final Query:

   • SELECT c.CustomerID: Selects customers who have ordered every product.

Example Data and Results

Assume you have the following data:

Products Table:

ProductID	ProductName
1	Widget A
2	Widget B
3	Widget C

OrderDetails Table:

OrderID	ProductID	Quantity
1	1	10
2	2	5
3	3	8
4	1	6
5	2	3
6	3	7

Orders Table:

OrderID	CustomerID
1	1001
2	1001
3	1002
4	1003
5	1003
6	1003

The CustomerProductCount CTE might produce:

CustomerID	OrderedProducts
1001	3
1002	1
1003	3

The ProductCount CTE would produce:

TotalProducts
3

The CustomersWithAllProducts CTE would identify:

CustomerID
1001
1003

The final result would be:

CustomerID
1001
1003

This result shows customers who have ordered every product available.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find customers who have ordered every product.