sql - MYSQL | Query to Get Leaves Allocated and Leaves Taken for Employee and should show the category of leave -

trying solve mystery morning , banging head on brick wall.

let me give background.

employees allocated leaves in different category e-g sick etc etc..

now if employee want take leave submit application approved someone.

now here comes part want records , show on page, want employee took how many leaves , how many leaves assigned him/her have show leaves in category base, depends on category assigned.

it confusing put schema screenshots.

this query have tried far.. problem in query getting total leaves taken total general, mean counts category leaves. should show how many leaves employee has taken in category

select  e.employee_id,  e.full_name,  le.no_of_leaves allocatedleaves, mllt.leave_type leavetypename, mllt.`ml_leave_type_id` leavetypeid,  ifnull(la.totalleavestaken,0) totalleavestaken,  (le.no_of_leaves - ifnull(la.totalleavestaken,0)) balance employee e inner join leave_entitlement le on e.employee_id = le.employee_id , month(le.`date_approved`) = 11 left join ml_leave_type mllt on mllt.ml_leave_type_id = le.ml_leave_type_id left join (select la.employee_id, count(1) totalleavestaken , la.`leave_application_id`            leave_approval la             month(la.approval_date) = 11            group la.employee_id          ) la on e.employee_id = la.employee_id left join leave_application lapp on lapp.application_id = la.leave_application_id left join ml_leave_type mlltla on mlltla.ml_leave_type_id = lapp.ml_leave_type_id e.`employee_id` = 1 group e.employee_id,leavetypename; 

result getting query

if see total leaves taken employee_id =1 2 both leavetypeids different in below table screenshot

try this:

select  e.employee_id,  e.full_name,  le.no_of_leaves allocatedleaves, mllt.leave_type leavetypename, mllt.`ml_leave_type_id` leavetypeid,  ifnull(la.totalleavestaken,0) totalleavestaken,  (le.no_of_leaves - ifnull(la.totalleavestaken,0)) balance employee e inner join leave_entitlement le on e.employee_id = le.employee_id , month(le.`date_approved`) = 11 left join ml_leave_type mllt on mllt.ml_leave_type_id = le.ml_leave_type_id left join (select la.employee_id, count(1) totalleavestaken , lapp.`ml_leave_type_id`            leave_approval la             inner join leave_application lapp on lapp.application_id = la.leave_application_id            month(la.approval_date) = 11            group la.employee_id, lapp.`ml_leave_type_id`           ) la on e.employee_id = la.employee_id , la.`ml_leave_type_id` = le.ml_leave_type_id e.`employee_id` = 1 group e.employee_id, leavetypename;