How can I get a value when the master table of the outer join is empty?

select @paytype := 'card';

INSERT INTO PAYMENT (Pay_TotalAmount, Pay_Date, Pay_Type, KEY_ID, CUST_id)
(select IFNULL(s1, 0)+IFNULL(s2, 0)+IFNULL(s3, 0)+IFNULL(s4, 0), current_timestamp(), 
@paytype
, t1.key_id, t1.cust_id from
            (select cust_id,key_id,sum(serreq_totalamount) s1
                from servicerequirement
                    group by key_id) t1
                        left outer JOIN 
            (select cust_id,key_id,sum(facreq_totalamount) s2
                from facilityrequirement
                    group by key_id) t2 ON t1.key_id = t2.key_id
                        left outer JOIN
            (select cust_id,key_id,sum(resorder_totalamount) s3
                from restaurantorder
                    group by key_id) t3 ON t2.key_id = t3.key_id
                        left outer JOIN
            (select cust_id,key_id,sum(fine_amount) s4
                from fine
                    group by key_id) t4 ON t3.key_id = t4.key_id
                    );

I have created a table where I get the sum of each item.

However, this query returns : 0 row affected.

I know what the problem is. If the key_id and cust_id are not in the t1 table specified as an alias, the value cannot be returned because the t1 table is empty.

If there is data for key_id in t1, this query works fine. However, there may be no data in t1. How can I solve this problem?

Answer

You want a full join, which MySQL does not support. There are various ways to approach this, but perhaps the simplest in your case is union all with aggregation afterwards:

INSERT INTO PAYMENT (KEY_ID, CUST_id, Pay_TotalAmount, Pay_Date, Pay_Type)    
    select key_id, cust_id,
           sum(serreq_totalamount + facreq_totalamount + resorder_totalamount + fine_amount),
           current_timestamp(), 
           @paytype
    from ((select cust_id, key_id, serreq_totalamount, 0 as facreq_totalamount, 0 as resorder_totalamount, 0 as fine_amount
           from servicerequirement
          ) union all
          (select cust_id, key_id, 0 as serreq_totalamount, facreq_totalamount, 0 as resorder_totalamount, 0 as fine_amount
           from facilityrequirement
          ) union all
          (select cust_id, key_id, 0 as serreq_totalamount, 0 as facreq_totalamount, resorder_totalamount, 0 as fine_amount
           from restaurantorder
          ) union all
          (select cust_id, key_id, 0 as serreq_totalamount, 0 as facreq_totalamount, 0 as resorder_totalamount, fine_amount
           from fine
          )
         ) ck
    group by cust_id, key_id