[Solved] How to SELECT and INSERT until ordered quantity is completed using nested query in MySQL

Question

I hope and imagine that there must be a more performant solution, but anyway, consider the following:

Schema (MySQL v8.0)

DROP TABLE IF EXISTS product_batches;

CREATE TABLE product_batches
(batch_number SERIAL PRIMARY KEY
,product_id INT NOT NULL
,quantity_available INT NOT NULL
);

INSERT INTO product_batches VALUES
( 1,1,15),
( 3,1, 5),
( 7,1,20),
(10,1,30),
(11,1,50);

Query #1

SELECT batch_number
     , product_id
  FROM 
     ( SELECT x.*
            , COALESCE(LAG(SUM(y.quantity_available),1) OVER (ORDER BY batch_number),0) running
         FROM product_batches x 
         JOIN product_batches y
           ON y.product_id = x.product_id 
          AND y.batch_number <= x.batch_number 
        GROUP 
           BY x.batch_number
      ) a 
  WHERE running <=22;

batch_number	product_id
1	1
3	1
7	1

View on DB Fiddle

Edit:

Not tested extensively, but for older versions, I think you can do something like this (performance is probably terrible though), so seriously consider upgrading:

   SELECT x.*
        , z.total - SUM(y.quantity_available) running
     FROM product_batches x 
     JOIN product_batches y
       ON y.product_id = x.product_id 
      AND y.batch_number >= x.batch_number
     JOIN (SELECT product_id, SUM(quantity_available) total FROM product_batches GROUP BY product_id) z
       ON z.product_id = x.product_id
    GROUP 
       BY x.batch_number
   HAVING running <= 22;

Edit 2:

Perhaps this is clearer:

SELECT x.*
        , GREATEST(SUM(y.quantity_available)-22,0) balance
     FROM product_batches x 
     JOIN product_batches y
       ON y.product_id = x.product_id 
      AND y.batch_number <= x.batch_number
    GROUP 
       BY x.batch_number
   HAVING balance < quantity_available

https://www.db-fiddle.com/f/DHED9dyxR2gMWaStyBZbN/0

Accepted Answer