0

Trying to write a sub query

SELECT ProductName 
FROM dbo.Products 
WHERE ProductID = (
    SELECT ProductID 
    FROM dbo.OrderDetails 
    WHERE Quantity >= 120
);

After executing I get this:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I'm sure it's close and this is beginner material but I've spent way too long on this.

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Nathan Murray
  • 9
  • 2

1 Answers1

1

I believe you're looking for the IN keyword; e.g.,

SELECT ProductName
    FROM dbo.Products
    WHERE
        ProductID IN
            (
                SELECT ProductID
                    FROM dbo.OrderDetails
                    WHERE Quantity >= 120
            )

The = comparison operator does single value comparisons, while the IN comparison operator asks if the left value is in the set on the right. Check out MySQL 8.0 Operators.

Technically, a correlated sub-query references the outer table. In this case, your sub-query is standalone and uncorrelated (e.g., SELECT ProductID FROM dbo.OrderDetails WHERE Quantity >= 120 can be executed as a standalone query). An example of a correlated sub-query might be:

SELECT ProductName
    FROM dbo.Products AS Outer
    WHERE
        ProductID IN
            (
                SELECT ProductID
                    FROM dbo.OrderDetails
                    WHERE
                        Quantity >= 120
                        AND Quantity <= Outer.Available
            );

On a loosely related note, consider using JOINs in place of sub-queries whenever possible, as JOINs are generally more efficient; e.g.,

SELECT ProductName
    FROM
        dbo.Products p
        JOIN dbo.OrderDetails od ON ( p.ProductID = od.ProductID )
    WHERE
        od.Quantity >= 120
ahi324
  • 369
  • 1
  • 8