Elad Jarby Elad Jarby - 4 years ago 97
SQL Question

additional help Northwind Example SQL

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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download