Design Assistance

[Guest]

I have been given a request that I am in a quandary as to how to proceed.
Mgmt has asked for a list of all employees who have perfect attendance by
week and month. I have an Attendance table that shows when the person is off
and an interval week calendar. But I only want people who do not have an
entry in the Attendance table for a week. Ideas?

 

[Douglas J. Steele]

SELECT ID, EmployeeName FROM Employees
WHERE ID NOT IN (SELECT ID FROM Absences)
ORDER BY EmployeeName

or

SELECT Employees.ID, Employees.EmployeeName
FROM Employees LEFT JOIN Absences
ON Employees.ID = Absences.ID
WHERE Absences.ID IS NULL
ORDER BY EmployeesEmployeeName

 

[Pieter Wijnen]

or normally the fastest method:

SELECT ID, EmployeeName FROM Employees
WHERE NOT EXISTS (SELECT 'X' FROM Absences WHERE Absences.ID=Employees.ID)
ORDER BY EmployeeName

Pieter