CREATE OR REPLACE VIEW vw_employee AS
SELECT em.*, (em.payable_amt-em.pay_amt) AS due_amt FROM(
SELECT e.*, ed.name AS designation, IFNULL(ae.payable_amt,0) AS payable_amt, IFNULL(ep.amt,0) AS pay_amt FROM employees AS e
LEFT JOIN emp_designation AS ed ON e.desig_code=ed.id
LEFT JOIN (SELECT emp_id, SUM(IFNULL(payable_amt, 0)) AS payable_amt FROM assign_employee GROUP BY emp_id) AS ae ON e.id=ae.emp_id
LEFT JOIN (SELECT emp_id, SUM(IFNULL(amt, 0)) AS amt FROM employee_payment GROUP BY emp_id) AS ep ON e.id=ep.emp_id) AS em;