Get the first and last names of all employee who entered the project at the same time as at least one other employee

Question

SELECT 
    EmpFname
    ,EmpLname 
FROM 
    Employee_m
WHERE 
    (
    SELECT 
        COUNT (w1.EnterDate) 
    FROM 
        WorksOn_m w1,WorksOn_m w2 
    WHERE 
        w1.EnterDate = w2.EnterDate
    ) >= 1

I tried this but the output gives all the names as the same row is compared. Any way around this?

my data:

Answer 1

You are missing conditions that say that the employee is working on the project in w1, and that the project in w2 is the same as the project in w1 but that the employee in w2 is not the same as the employee in w1.

The reason you get all rows is because the where condition always evaluates to true, for all employees. That is because the sub query simply asks for at least one row that has the same date in both w1 and w2. Consider that WorksOn_m has only one row:

WorksOn_m -------------- EnterDate 12/03/2014

Is there a record in both w1 and w2 with the date 12/03/2014? Yes, because they're the same table. Are you applying any other conditions in the WHERE clause? No. So you'll get all employees, because you're basically saying "Give me all employees WHERE 1=1".

Answer 2

You will have to fill in the join condition since your post does not reveal those details:

SELECT EmpFname, EmpLname
FROM (
    SELECT EmpFname
         , EmpLname
         , row_number() over (order by EnterDate asc) as r1
         , row_number() over (order by EnterDate desc) as r2
    FROM Employee_m
    JOIN WorksOn_m 
        ON ...
) AS T 
WHERE 1 IN (r1,r2)