Why Does My SQL Subquery Return a 'Subquery returned more than 1 value' Error?

SQL Server Subquery Returning Multiple Rows: Troubleshooting and Solution

The following SQL query exemplifies a common problem: a subquery returning more than one value, leading to an error.

<code class="language-sql">SELECT 
   orderdetails.sku,
   orderdetails.mf_item_number,
   orderdetails.qty,
   orderdetails.price,
   supplier.supplierid,
   supplier.suppliername,
   supplier.dropshipfees,
   cost = (SELECT supplier_item.price
           FROM   supplier_item,
                  orderdetails,
                  supplier
           WHERE  supplier_item.sku = orderdetails.sku
                  AND supplier_item.supplierid = supplier.supplierid)
FROM   orderdetails,
       supplier,
       group_master
WHERE  invoiceid = '339740'
       AND orderdetails.mfr_id = supplier.supplierid
       AND group_master.sku = orderdetails.sku  </code>

Executing this query results in the error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, >, >= or when the subquery is used as an expression."

The Problem: Multiple Row Subquery Result

The error arises because the subquery (SELECT supplier_item.price...) can return multiple price values. This is because the WHERE clause doesn't uniquely identify a single supplier_item record for each orderdetails record. A subquery used in this way requires a single, unambiguous result.

The Solution: Using JOINs for Efficient Data Retrieval

The efficient and correct approach is to replace the subquery with explicit JOIN operations:

<code class="language-sql">SELECT
    od.Sku,
    od.mf_item_number,
    od.Qty,
    od.Price,
    s.SupplierId,
    s.SupplierName,
    s.DropShipFees,
    si.Price as cost
FROM
    OrderDetails od
    INNER JOIN Supplier s on s.SupplierId = od.Mfr_ID
    INNER JOIN Group_Master gm on gm.Sku = od.Sku
    INNER JOIN Supplier_Item si on si.SKU = od.Sku and si.SupplierId = s.SupplierID
WHERE
    od.invoiceid = '339740'</code>

This revised query uses INNER JOIN to connect OrderDetails, Supplier, Group_Master, and Supplier_Item tables based on matching Sku and SupplierId. This ensures that only matching rows are combined, preventing the subquery from returning multiple values. The si.Price is directly selected as cost, providing the correct price for each order detail. This method is far more efficient and readable than using a correlated subquery in this scenario.

The above is the detailed content of Why Does My SQL Subquery Return a 'Subquery returned more than 1 value' Error?. For more information, please follow other related articles on the PHP Chinese website!