MySQL split table amounts based on another table information

I need to split amounts in a table (transactions) accord to another related table information (bill_details).

Base table (transactions):

ID	amount	document_id
1	100.00	11
2	80.00	12
3	120.00	13

Another table (bill_details):

ID	amount	document_id	description
1	20.00	11	A
2	60.00	11	B
3	20.00	11	C
4	80.00	12	D
5	60.00	13	E
6	20.00	13	F
5	20.00	13	G
6	40.00	13	H

What I need:

amount (splited from transactions)	description (from row in description)
20.00	A
60.00	B
20.00	C
80.00	D
60.00	E
20.00	F
20.00	G
20.00	H

Notes: Sometimes the sum of ‘bill_details’ amount is not equal to the ‘transactions’ amount. A bill can have 0+ transactions.

should I keep trying to do it in MySQL or should I use PHP?

Answer

It’s bit complicate but in MySQL 8.0 you can use window function in next way:

SELECT 
    description,
    bill_details.amount + least(
        transactions.amount -
        SUM(bill_details.amount) OVER (PARTITION BY document_id ORDER BY bill_details.ID),
        0
    ) amount
    
FROM bill_details
JOIN transactions USING(document_id)
ORDER BY bill_details.ID

Live SQL fiddle