MYSQL : Get data from multiple table using JOIN and UNION in single row

Question

I have three database tables ‘customer’, ‘billing’, and ‘transaction’

customer:

cuid	name	address
1	David	City 1
2	Roja	City 2

billing :

id	cuid	month	bill_amount
1	1	1	100
2	1	2	200
3	2	1	400
4	1	3	100

transaction:

id	cuid	date	received_amount
1	1	2022-3-02	250
2	2	2022-2-02	200
3	2	2022-3-02	200

I need a new generated Due table after calculating month wise due amount using FIFO like this:

cuid	Name	Address	Month	Due_Amount
1	David	City 1	2	50
1	David	City 1	3	100

This code did not work properly. MySql Code:

SELECT 
    due.cuid,
    due.Name,
    due.Address,
    due.Month,
    sum(due.Amount - due.Received) AS Due_Amount
FROM 
    (SELECT 
        c.cuid,
        c.name AS name,
        c.address AS address,
        b.month AS Month,
        0 AS Received,
        b.bill_amount AS Amount
    FROM 
        customer c
        INNER JOIN billing b ON c.cuid=b.cuid
    
    UNION ALL
    SELECT 
        c.cuid,
        c.name AS Name,
        c.address AS Address,
        null AS Month,
        t.received_amount AS Received,
        0 AS Amount
    FROM 
        customer c
        INNER JOIN transaction t ON c.cuid = t.cuid) AS due
GROUP BY 
    due.cuid;

Code generated table is :

cuid	Name	Address	Month	Due_Amount
1	David	City 1	1	150
2	Roja	City 2	1	0

Answer 1

You probably don't need all that SUM(), UNION ALL and subquery. You can do it in a single query and JOIN all three tables like this:

SELECT c.cuid,
       c.Name,
       c.Address,
       b.Month,
       t.received_amount-b.bill_amount AS Due_Amount
FROM customer c 
  JOIN billing b 
    ON c.cuid=b.cuid
  JOIN transaction t 
    ON c.cuid=t.cuid
    AND b.month=MONTH(t.date);

Demo fiddle

I do have a slight curiosity though because my understanding of Due_amount is the remaining amount need to be paid by the customer and yours doesn't seem to be one. If it is, then the value should be negative because the customer David paid more (received_amount) than the bill_amount.