I made some changes in the table structure to make the query cleaner. I removed the columns "emp_id", "dept_id", and "loan_adv_no" from the table "loans_deduction_details" and added a column to hold the id of the good row of "Loans_Advances_Master" to act as a foreign key.
Here is my what my fiddle looks like.
create table LAM (id int, depId varchar(20), empId varchar(20), loanId varchar(20), loanAmt int);
create table LDD(id int, lamId int, loadDAmt int);
insert into LAM values (1,'De_1','emp_1', 'LA_1', 10000), (2,'De_1','emp_1', 'LA_2', 10000);
insert into LDD values (1,1,5000),(2,1,5000),(3,2,5000);
Now that the tables are initialized I just need to query them.
select temp.depID, temp.empID, temp.loanId, temp.loadAmt
from (
select
LAM.depID as depID,
LAM.empID as empID,
LAM.loanId as loanId,
LAM.loanAmt - SUM(LDD.loadDAmt) AS loadAmt
FROM LDD
INNER JOIN LAM ON LDD.lamId = LAM.Id
GROUP BY LDD.lamId
) temp;
You can also check the fiddle
here[
^].