troubleshooting Question

Need help with a complex SQL query

Avatar of rrattie
rrattieFlag for United States of America asked on
Microsoft SQL Server 2005ColdFusion LanguageSQL
16 Comments1 Solution291 ViewsLast Modified:
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')


ASKER CERTIFIED SOLUTION
ralmada

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros