additional help Northwind Example SQL

Question

I have question about the Northwind SQL Server sample database that I don't know how to solve it

Show CustomerID for all customers who have at least three different products from all orders, but never ordered 2 products from the same category.

i didn't know how to check "but never ordered 2 products from the same category"

please help me :) Code I tried for this question:

SELECT
    c.CustomerID,COUNT(DISTINCT p.ProductID)
FROM 
    Customers c
JOIN 
    Orders o ON o.CustomerID = c.CustomerID
JOIN 
   [Order Details] od ON od.OrderID = o.OrderID
JOIN 
   Products p ON p.ProductID = od.ProductID
GROUP BY
   c.CustomerID
HAVING
   COUNT(DISTINCT p.ProductID) >= 3 

I've been stuck on these query for hours, please help guys!

This is link for Northwind sample database: https://northwinddatabase.codeplex.com/

Answer 1

Add one more condition to check if the number of distinct products equal the number of distinct categories. This makes sure that there is always one and only one product from each category.

SELECT 
c.CustomerID,COUNT(DISTINCT p.ProductID), count(distinct c.categoryid)
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
GROUP BY c.CustomerID
HAVING COUNT(DISTINCT p.ProductID) >= 3 
and count(distinct c.categoryid) = COUNT(DISTINCT p.ProductID)