0

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)?

8
  • 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 eld.emp_id = emp.PK 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' - the employee table all data u view?
    – SNS
    Jul 11, 2019 at 11:14
  • @KGR Sorry this brought the same result. Jul 11, 2019 at 11:16
  • give the table structure.
    – SNS
    Jul 11, 2019 at 11:16
  • which table strucutre? Jul 11, 2019 at 11:26
  • your 3 table structure.
    – SNS
    Jul 11, 2019 at 11:26

1 Answer 1

1

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
9
  • not this brought the same result. Jul 11, 2019 at 11:25
  • this will for sure bring all the employees .. I do not know if the data calculated will be ok Jul 11, 2019 at 11:29
  • I don't know but i have run this query and still getting only single record(emp). Jul 11, 2019 at 12:30
  • Can you please share the 3 table structure? Jul 11, 2019 at 13:08
  • oh actually i find that sum fuction in the select is the problem. Jul 11, 2019 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.