Employees Hired Within the Same Time Period

Question

I am running into a weird difficulty with my query logic when trying to see if two employees work within the same time period.

I have an employees table with the employee id (emp_ID) and their (start_Date) and (end_Date) which are both DATE types stored as "yyyy-mm-dd".

My query is this (E1 started 2006-08-27 and ended 2009-05-14 and E2 started 2007-01-13 and ended 2008-06-25, in other words, they worked within the same time period):

SELECT *
FROM employees e1, employees e2
WHERE e1.emp_ID = '1' AND e2.emp_ID = '2'
AND e1.start_Date <= e2.start_Date AND e1.end_Date >= e2.end_Date;

If I were to flip the two employee ID's, it does not return anything? There is something missing with my logic but I am struggling to find it. Thank you for your time and help.

Answer 1

If I follow you correctly, you just need to fix the logic to properly check if the employment periods overlap:

select e1.emp_id emp_id_1, e2.emp_id emp_id_2
from employees e1
inner join employees e2
    on e1.start_date <= e2.end_date and e1.end_date >= e2.start_date
where e1.emp_id = 1 and e2.emp_id = 2

Notes:

• use standard, explicit joins (with the on keyword) rather than old-school, implicit joins (with a comma in the from clause)

• it looks like emp_id is a number, so it should be treated as such (no single quotes around the literal value)

• select * is not a good pick here: that's a self-join, so column names are ambiguous in the resultset; do enumerate and alias the columns in the select clause