Row with an Operation of three subqueries - Mysql

Question

I am trying to make a query with 3 fields, one of them is the type of a product, another is today's date and another is a calculation of two tables, the problem I have is with the calculation of the two tables.

I can not show all the records because this calculation shows several records and therefore the query gives an error: "Operand should contain 1 column" so if I put a limit 1 to these queries it works fine, but only shows a record and I want that shows all that exist.

To do what I need depends on three tables:

• Table Orders: id, fecha_entrega, unidades, id_formatopeso ... translate: id, date_delivery, units, id_weightformat

• Table weight format: id_formatopeso, tipo_formato, stock_minimo ... translate: id_weightformat, format_type, minimum_stock

• Table Stock: id_stock, stock_pizza, fecha, id_formatopeso translate: id_stock, stock_pizza, date, id_weightformat I need an operation that calculates:

The query add subquery is:

1. The units of the orders table with today's delivery date of each product (weight format)
2. Subtract The stock_pizza of the last record in the stock table of each product (weight format)
3. Sum The stock_minimo that each product has (weight format)

Table Pedidos(Orders):

+----------------+---------------+----------+----------------+
| id_formatopeso | fecha_entrega | unidades | id_formatopeso |
+----------------+---------------+----------+----------------+
|              2 | 2017-10-25    |       50 |              2 |
|              7 | 2017-11-09    |      450 |              7 |
|              6 | 2017-11-09    |      500 |              6 |
+----------------+---------------+----------+----------------+

Tabla Formatopeso(Weight format)

+----------------+--------------+--------------+
| id_formatopeso | tipo_formato | stock_minimo |
+----------------+--------------+--------------+
|              2 | 12Ø 70gr     |          150 |
|              7 | 20Ø 150gr    |          150 |
|              6 | 22Ø 180gr    |          150 |
+----------------+--------------+--------------+

Tabla Stock

+----------+-------------+------------+----------------+
| id_stock | stock_pizza | fecha      | id_formatopeso |
+----------+-------------+------------+----------------+
|        2 |          50 | 2017-10-25 |              2 |
|        7 |         200 | 2017-11-09 |              7 |
|        6 |         300 | 2017-11-09 |              6 |
+----------+-------------+------------+----------------+

Pedidos tipo_formato: 20Ø 150gr id=7; 450(unidades table orders) – 200(stock_pizza table stock) + 150 (minimum_stock table stock) = 400(Pizza_calculo)

Pedidos tipo_formato: 22Ø 180gr id=6; 500(unidades table Orders) – 300(stock_pizza table stock) + 150(minimum_stock table stock) = 350(Pizza_calculo)

I need show something like this:

+--------------+---------------+-------------+
| format_type  |pizza_operation|  date_today |
+--------------+---------------+-------------+
| tipo_formato | pizza_calculo |  fecha_hoy  |
+--------------+---------------+-------------+
| 20Ø 150gr    |           400 |  2017-11-09 |
+--------------+---------------+-------------+
| 22Ø 180gr    |           350 |  2017-11-09 |
+--------------+---------------+-------------+

My query not working:

SELECT formatopeso.tipo_formato, 
   (SELECT unidades FROM pedidos WHERE DATE(fecha_entrega) = DATE(NOW())) 
   -
   (SELECT stock_pizza FROM stock WHERE fecha = (SELECT MAX(fecha) FROM stock)LIMIT 1)
   +
   (SELECT stock_minimo FROM formatopeso LIMIT 1)
   as pizza_calculo, 
   (SELECT CURDATE())
   as fecha_hoy
FROM pedidos
INNER JOIN formatopeso
ON formatopeso.id_formatopeso = pedidos.id_formatopeso
WHERE pedidos.fecha_entrega IN (CURDATE())
GROUP BY formatopeso.tipo_formato;

Answer 1

You should be joining the tables, not using subqueries in SELECT. Use the technique in SQL select only rows with max value on a column to get the most recent record in the stock table for each product.

SELECT f.tipo_formato, p.unidades - s1.stock_pizza + f.stock_minimo AS pizza_calculo, p.fecha_entrega AS date_today
FROM pedidos AS p
JOIN formatopeso AS f ON f.id_formatopeso = p.id_formatopeso
JOIN stock AS s1 ON s1.id_formatopeso = p.id_formatopeso
JOIN (SELECT id_formatopeso, MAX(fecha) AS max_fecha
      FROM stock
      GROUP BY id_formatopeso) AS s2
    ON s1.id_formatopeso = s2.id_formatopeso AND s1.fecha = s2.max_fecha
WHERE p.fecha_entrega = CURDATE()

DEMO