我怎样才能在mysql中纠正以下错误（子查询返回超过1行）

Question

I have five tables such as tb_rawmaterial,tb_ingredients,tb_food, tb_order_items, tb_stock.

tb_rawmaterial has all the rawmaterial for a food

tb_ingredients connects the tb_food and the tb_rawmaterial with food_id as foreign key

tb_orderitems connects the tb_foods with food_id as foreign key.

tb_stock has all the rawmaterial stock level details it connects tb_rawmaterial with material_id as foreign key.

now what i have to do is :

i have to write an update query to change the stock level when make an order for a food, i wrote the below query:

UPDATE tb_stock set tb_stock.quantity = (tb_stock.quantity-(
    SELECT tb_ingredients.usage_volume * sum(tb_order_item.qty)
    FROM tb_ingredients 
    INNER JOIN tb_order_item 
    ON(tb_order_item.food_id=tb_ingredients.food_id
    AND tb_order_item.order_id='ORD-T01-00289') 
    GROUP BY tb_ingredients.food_id )) 
    WHERE material_id=(SELECT tb_ingredients.material_id
    FROM tb_ingredients 
INNER JOIN tb_order_item 
ON(tb_order_item.food_id=tb_ingredients.food_id
AND tb_order_item.order_id='ORD-T01-00289')
GROUP BY tb_ingredients.food_id)

BUT

it works if the raw material data is only one, if the same raw material data repeat again it gives the below error:

Subquery returns more than 1 row

How can i rectify this? Please advice me

Answer 1

in order to limit the rows of the sub-queries results ...

you can either add LIMIT 1 to the sub-query

or select by SELECT DISTINCT/GROUP BY.

using LEFT JOIN instead of INNER JOIN might also return less records.