Mysql return value with nested Select

Question

I am writing some software for a restaurant. They change prices on certain dates. I have two tables, one with the products ordered by order, and another with prices, historical, that is, every time they change a price, I set a bit to 0 to the old value, create new entry and assign bit 1 into "valid" column. I use the following MySQL code to recall the valid actual price:

`

SELECT 
`tbl_precios_productos`.`dbl_precio`
    FROM `tbl_precios_productos` 
    left join `tbl_prods_x_orden` on `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    where `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    and `tbl_precios_productos`.`bit_activo` = 1
    And `tbl_precios_productos`.`int_tipo_precio` = 1
    and `tbl_prods_x_orden`.`int_producto_id` = 10 /* this is the ID of the product */
    GROUP BY `tbl_precios_productos`.`dbl_precio`

`

When I try to nest that code into a bigger one, it doesn't work. Here is what I tried: `

SELECT   
ANY_VALUE( `tbl_productos`.`id_producto`) AS `ID Prod`,  
ANY_VALUE( `tbl_productos`.`chr_nombre_prod`) AS `Producto`,  
SUM(ANY_VALUE( `tbl_prods_x_orden`.`int_cantidad`)) AS `Cantidad`, 
(SELECT 
`tbl_precios_productos`.`dbl_precio`
    FROM `tbl_precios_productos` 
    left join `tbl_prods_x_orden` on `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    where `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    and `tbl_precios_productos`.`bit_activo` = 1
    And `tbl_precios_productos`.`int_tipo_precio` = 1
    and `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    GROUP BY `tbl_precios_productos`.`dbl_precio`) AS `Precio`,
ANY_VALUE( `tbl_tipos_precios`.`id_tipo_precio`) AS `Tipo Precio`,  
ANY_VALUE( `tbl_tipos_precios`.`chr_nombre_precio`) AS `CHRTipoPrecio`,  
ANY_VALUE( `tbl_ordenes_cerradas`.`int_forma_pago`) AS `ID TPago`,  
ANY_VALUE( `tbl_formas_pago`.`chr_forma_pago`) AS `Tipo Pago`,  
ANY_VALUE( `tbl_productos`.`fl_ordenar`) AS `Ordenar`  
From  `tbl_prods_x_orden` 
LEFT JOIN `tbl_productos` ON `tbl_prods_x_orden`.`int_producto_id` = `tbl_productos`.`id_producto` 
LEFT JOIN `tbl_precios_productos` ON (((`tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`) And ( `tbl_precios_productos`.`int_tipo_precio` = 1)))
LEFT JOIN `tbl_precio_tipo_ordenes` ON `tbl_prods_x_orden`.`int_orden_id` = `tbl_precio_tipo_ordenes`.`id_orden`  
LEFT JOIN `tbl_tipos_precios` ON `tbl_tipos_precios`.`id_tipo_precio` = `tbl_precio_tipo_ordenes`.`id_tipo_precio`  
LEFT JOIN `tbl_ordenes_cerradas` ON `tbl_ordenes_cerradas`.`id_orden_id` = `tbl_prods_x_orden`.`int_orden_id`  
left join `tbl_formas_pago` ON `tbl_ordenes_cerradas`.`int_forma_pago` = `tbl_formas_pago`.`id_forma_pago`  
WHERE  `tbl_productos`.`int_activo` = 1  
And `tbl_prods_x_orden`.`bool_activo` = '1'  
and `tbl_ordenes_cerradas`.`int_forma_pago` = 3
And `tbl_ordenes_cerradas`.`id_control_fecha` >= 101  
And `tbl_ordenes_cerradas`.`id_control_fecha` <= 101
group BY `ID Prod`, `Tipo Precio`, `ID TPago`  
order by `Ordenar`

` I get an error that the subquery returns more than one result. How can I get the specific valid price from the prices table using the id of the product. Imagine prices table with 4 columns: id, id_producto, valid, date_set. Sorry some of the code is in spanish, that is my mother language and what I am using to program, but I hope you get the idea. Am I doing it correctly or is there any other way? Thanks in advance.