Okay here is what I am trying to do.
I want to find all the employees who have NOT completed a certain training course.
I can easily find all those employees who have completed, but I cannot for the life of
me figure out how to find those who have not.
The tables and columns used are below.
The employee status must be active and completed is yes for those who have completed the course.
If the employee has not completed the course, they won't have any records for that class in the usertraining table.
Table: (column, column, etc..)
employees: (ID, last_name, first_name, dept, status)
courses: (id, course_name)
usertraining: (empID, courseID, completed)
This is what I have so far, that pulls the completed list.
SELECT dbo.employees.ID, dbo.courses.id AS tc.id, dbo.employees.last_name, dbo.employees.first_name, dbo.employees.dept, dbo.employees.status,
dbo.courses.course_name, dbo.usertraining.empID, dbo.usertraining.courseID, dbo.usertraining.completed
FROM dbo.employees INNER JOIN
dbo.usertraining ON dbo.employees.ID = dbo.usertraining.empID INNER JOIN
dbo.courses ON dbo.usertraining.CourseID = dbo.courses.id
WHERE (dbo.employees.status = 'active') AND (dbo.usertraining.completed = 'yes')