Mysql – How to use left join with Where clause

join;MySQL

I was thinking that I have a very good understanding about my sql and joins but i miserably failed to make this query. I have to make employee leave report, therefore I want all employee list no matter he make leave or not, if there is no leave then zero will be displayed. I have this query currently:

select
leave_detail_id, emp_id, Employee_Name, leave_type, leave_name, sum(leave_total_days) total_leave, leave_current_status_id
from employee emp
left join  tbl_emp_leave_details eld
ON emp.PK = eld.emp_id
inner join tbl_leave_types lt
ON lt.leave_id = eld.leave_type
where leave_current_status_id = 1
and leave_start_date >= '01-07-2019'
and leave_end_date <= '31-07-2019'

and emp.Active=1
group by leave_type

You can see that even after applying left join i am unable to bring all the employees. Please help me to understand the problem. I know that where filter is also apply to join. but how can i correct this with (an explanation)?

Best Answer

You might try this, however as I do not have any understanding of your schema, the result might not be what you search for.

select
  leave_detail_id
  ,emp_id
  ,Employee_Name
  ,leave_type
  ,leave_name
  ,sum(leave_total_days) total_leave
  ,leave_current_status_id
from employee emp
left join  tbl_emp_leave_details eld
  ON emp.PK = eld.emp_id
left join tbl_leave_types lt
  ON lt.leave_id = eld.leave_type
  and leave_current_status_id = 1
  and leave_start_date >= '01-07-2019'
  and leave_end_date <= '31-07-2019'
group by 
  leave_detail_id
  ,emp_id
  ,Employee_Name
  ,leave_type
  ,leave_name
  ,leave_current_status_id
Related Question