0

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!

2
  • Edit the question and provide a minimal reproducible example, i.e. the CREATE statements of the tables or other objects involved (paste the text, don't use images, don't link to external sites), INSERT statements for sample data (dito) and the desired result with that sample data in tabular text format.
    – sticky bit
    Apr 17, 2021 at 18:11
  • why has the customer id the same id as the product?
    – nbk
    Apr 17, 2021 at 18:45

2 Answers 2

2

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
3
  • Nice (+1) - don't have a running instance of SQL Server - would there be any difference in the efficiency of the two queries or does the optimiser fold them into one?
    – Vérace
    Apr 18, 2021 at 11:01
  • I think it would be exactly the same, but you can check the query plans to verify. Apr 18, 2021 at 15:11
  • Thank you so much! Apr 19, 2021 at 0:58
0

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

2
  • I finally have it figured it out. I just needed the HAVING COUNT to actually count the ProductID instead of just using an *. Thanks so much for the input! Apr 17, 2021 at 19:41
  • Kindly mark the answer as helpful and/or correct if you found it so!
    – Vérace
    Apr 17, 2021 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.