SQL Server Subqueries – Best Practices and Examples

sql serversubquery

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!

Best Answer

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
Related Question