Subquery

1

Hello! I have a task to use NorthWind database to write down products names which were sold in a quantity of less than 1000. I did not understand how to do it with subquery. I have already done it with JOIN operator, but now I need to do it with subquery. Could you help me please?
I try to explain what I do not understand. To do it without JOIN I merged tables with WHERE operator as Products.ProductID=[Order Details].ProductID, then I used group by and having sum<1000. But there is no subquery there.
My code where I started to write a query without JOIN, but in the end I understood that I did not use subquery. (It works, but I need to use subquery).
SELECT ProductName, SUM(Quantity) FROM Products, [Order Details]
WHERE Products.ProductID=[Order Details].ProductID
GROUP BY ProductName
HAVING SUM(Quantity)<1000
Thank you in advance!

2

That is actually trickier than it sounds. To use a true subquery, you have to add an outer query, like this:

SELECT *
FROM (
    SELECT ProductName, 
        (SELECT SUM(Quantity)
         FROM [Order Details]
         WHERE Products.ProductID=[Order Details].ProductID
        ) AS ProductQuantity
    FROM Products
    GROUP BY ProductName
) AS subquery
WHERE ProductQuantity < 1000

Here's a method using a derived table subquery:

SELECT ProductName, ProductQuantity
FROM Products
INNER JOIN (
    SELECT ProductID, SUM(Quantity) AS ProductQuantity
    FROM [Order Details]
    GROUP BY ProductID
    HAVING SUM(Quantity) < 1000
) AS [Order Details] ON Products.ProductID = [Order Details].ProductID
1 Like
3

Thank you very much!