Subqueries in SQL Server

Question

I am taking a SQL course and am completely stumped on this question.

Using a subquery have a query that returns: CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 product' as queryInfo The subquery from this query will be: All customers with who have ordered more than 1 product

Note: More than one Product, not Quantity > 1 So a customer who ordered 10 of the same product is NOT what I'm looking for.

Here is the full query I used:

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE c.CustomerID IN (
           SELECT p.ProductID 
             FROM Customers c 
             JOIN Orders o
                  ON c.CustomerID = o.CustomerID
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             JOIN Products p
                  ON oi.ProductID = p.ProductID 
         GROUP BY p.ProductID 
           HAVING COUNT(*) > 1
         )
     ;

Not sure what I am doing wrong, but any input is greatly appreciated. Thanks!

Answer 1

Your code has a number of issues:

• You can simplify to a COUNT(DISTINCT here as you want the number of distinct products
• The subquery returns ProductID you would need CustomerID instead. Since you need the grouped count, you are better off with an EXISTS query instead
• You are querying Customer again unnecessarily. Products is also not needed as you can get ProductID from OrderItems

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE EXISTS (SELECT 1
             FROM Orders o
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             WHERE c.CustomerID = o.CustomerID
           HAVING COUNT(DISTINCT oi.ProductID) > 1
         )
     ;

You could also remove the HAVING and check the COUNT(DISTINCT from the subquery on the outside:

 WHERE (SELECT COUNT(DISTINCT oi.ProductID)
             FROM Orders o
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             WHERE c.CustomerID = o.CustomerID
         ) > 1

Answer 2

What is wrong? From your point of view? But:

1. the subselect returns productid instead of customerid which means the data returned is wrong

2. the customer table and product table in subselect is bot needed. You can group by productid in orderitems table and return customerid from orderheader