SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

Question

I run the following query:

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   

I get the following error:

Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any ideas?

Answer 1

Try this:

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' 

This will return multiple rows that are identical except for the cost column. Look at the different cost values that are returned and figure out what is causing the different values. Then ask somebody which cost value they want, and add the criteria to the query that will select that cost.