0

I have two tables like below.

Attendance Table: emp_attendance
id,emp_id,attendance_date,status

Attendance Table: emp_leaves
id,emp_id,leave_from_date,leave_to_date,leave_type

What is the best way to join these two tables using emp_id? Incase if the emp_leaves table has more than one record for the same emp_id then the join will return two rows in the query results, this is what i have tried. 

SELECT emp_attendance.emp_id, emp_attendance.attendance_date,
CASE 
WHEN emp_attendance.attendance_date BETWEEN emp_leaves.leave_from_date AND emp_leaves.leave_to_date 
THEN emp_leaves.leave_type ELSE emp_attendance.Status end AS final_Status 
FROM emp_attendance LEFT JOIN emp_leaves 
ON emp_leaves.emp_id = emp_attendance.emp_id

kindly advice.

Felix
  • 571
  • 14
  • 34
davidb
  • 263
  • 5
  • 10
  • 23
  • what do you mean by "best practice to join"? The only point I can see here is that to fulfill the requirement "ncase if the emp_leaves table has more than one record for the same emp_id then the join will return two rows in the query results", you can just use standard JOIN, which results in better performances but leaves out rows with no emp_leaves. – dylaniato Aug 03 '17 at 15:34
  • It's behaving correctly. It all depends on what you want. If you have more than one records in emp_leaves for the same emp_id, of course it will return more than one record. – Eric Aug 03 '17 at 15:35
  • You could union the tables instead of joining them assuming a leave an attendance don't overlap. or you could get the ,most recent leave record instead of all; but as other indicated. This lacks a [MVCE](https://stackoverflow.com/help/mcve) to identify your issue you're looking for help with. Sample data expected results help! – xQbert Aug 03 '17 at 15:47
  • @Eric, sorry for not making the question clearer. I agree it is behaving correctly. Can you please suggest some method where we can omit the multiple result for same emp_id for same attendance_date after joining those two tables – davidb Aug 03 '17 at 15:50
  • @xQbert, I will have to create a new table for leaves something like this with thee fields, emp_id,attendance_date,leave_type, then I can union them or I can easily join them. – davidb Aug 03 '17 at 15:56
  • It depends on what you want really. I can't help you unless I know what you want. Why do you want to join them? What do you actually want to accomplish? emp_leaves can have multiple records. Which record do you want to get if there are multiple. – Eric Aug 03 '17 at 15:57
  • @davidb no, I'd just duplicate attendance_date twice so you have a from to with the same value. – xQbert Aug 03 '17 at 15:59
  • @xQbert, i think i should have a daywise table to overcome this issue. i have posted a new question for this https://stackoverflow.com/questions/45490612/mysql-get-dates-between-two-dates – davidb Aug 03 '17 at 17:09

0 Answers0