Cordial Greeting.
I am currently developing an academic project for a restaurant system, but I have several days stuck with a problem, I hope you can help me, first these are the relationships of the tables used:
What I try to do is that by doing a insert in the table imprimir_ticket (in other words, when a sale is made) I want to subtract stock_actual from table insumo based on the amount of input used by each sold dish (the inputs and their respective quantities used by each dish are defined in the%% of% table). Currently this is the query that I have:
update insumo i set i.stock_actual = round((i.stock_actual - (select pi.cantidad_insumo from platillo_insumo pi where pi.id_insumo = i.id_insumo and pi.id_platillo = (select tp.id_platillo from (select p.id_platillo from comanda c inner join detalle_comanda dc on c.id_comanda = dc.id_comanda
inner join detalle_platillo dp on dc.id_detalle_comandas = dp.id_detalle_comanda
inner join platillo p on dp.id_platillo = p.id_platillo where c.id_comanda = _id_comanda)as tp where tp.id_platillo = pi.id_platillo))),2)
where i.id_insumo in (select id_insumo from platillo_insumo where id_platillo in (select p.id_platillo from comanda c inner join detalle_comanda dc on c.id_comanda = dc.id_comanda
inner join detalle_platillo dp on dc.id_detalle_comandas = dp.id_detalle_comanda
inner join platillo p on dp.id_platillo = p.id_platillo where c.id_comanda = _id_comanda));
Everything is executed correctly and works as it should, as long as two or more dishes with the same input are not sold, I mean, for example if in the table platillo_insumo the platillo_insumo of a row is 25 and the id_platillo of the same row is 5 and in another row the id_insumo is 30 and the id_insumo is 5
I will generate the following error:
Error Code: 1242. Subquery returns more than 1 row
I hope you can help me. Thanks.