0
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:

enter image description here

  • Please provide the definition of the tables involved. It is not possible to answer this question at the moment unless we guess the existence of an EmployeeID column (and the name of it) in both tables. – Martin Smith Jul 13 '14 at 14:34
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".

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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