show records that are missing from a table based on another table

[Keffer]

I want to be able to show in a results set the status of the training plan
for all employees. I have a table that has an employee list, a table that
lists all required courses and then a table that shows all courses completed
by the employees. The employee is only listed in this last table if at least
one course has been taken.

Table: EmployeeList
EmployeeID, LastName
1111 Smith
2222 Doe
3333 Johnson

Table: RequiredCoursesList
CourseCode, CourseName
EDU01 Math
EDU02 Physics

Table: CoursesTaken
EmployeeID, CourseCode, CompletedDate
1111 EDU01 1/1/2006
1111 EDU02 1/1/2006
2222 EDU01 1/1/2006


I would like to get the following results showing that employee 1111 has
taken all required courses, employee 2222 has taken one but is missing one
and employee 3333 is missing all courses.

EmployeeID, CourseCode, CourseName, CompletedDate
1111 EDU01 Math 1/1/2006
1111 EDU02 Physics 1/1/2006
2222 EDU01 Math 1/1/2006
2222 EDU02 Physics NULL
3333 EDU01 Math NULL
3333 EDU02 Physics NULL

I suspect I need some sort of left or right join, but I've not been able to
get what I'm looking for. I'm a novice when it comes to queries. Thanks for
any help.

 

[Ofer Cohen]

Try something like

SELECT EmployeeID, LastName, CourseCode , CourseName , (SELECT CompletedDate
FROM CoursesTaken
WHERE (((CoursesTaken.EmployeeID)=T2.[EmployeeID]) AND
((CoursesTaken.CourseCode)=T1.[CourseCode]))
) AS DateComplete
FROM RequiredCoursesList AS T1, EmployeeList AS T2

 

[Keffer]

Thanks so much. This does exactly what I wanted. And to think that you came
back with the answer in such short time when I wrestled for hours today
trying to get a LEFT join and searching the discussion groups for similar
queries... I'm glad I posted the question before spending any more time.
You've been very helpful. Thanks!

Try something like

SELECT EmployeeID, LastName, CourseCode , CourseName , (SELECT CompletedDate
FROM CoursesTaken
WHERE (((CoursesTaken.EmployeeID)=T2.[EmployeeID]) AND
((CoursesTaken.CourseCode)=T1.[CourseCode]))
) AS DateComplete
FROM RequiredCoursesList AS T1, EmployeeList AS T2

--
Good Luck
BS"D

I want to be able to show in a results set the status of the training plan
for all employees. I have a table that has an employee list, a table that
lists all required courses and then a table that shows all courses completed
by the employees. The employee is only listed in this last table if at least
one course has been taken.

Table: EmployeeList
EmployeeID, LastName
1111 Smith
2222 Doe
3333 Johnson

Table: RequiredCoursesList
CourseCode, CourseName
EDU01 Math
EDU02 Physics

Table: CoursesTaken
EmployeeID, CourseCode, CompletedDate
1111 EDU01 1/1/2006
1111 EDU02 1/1/2006
2222 EDU01 1/1/2006


I would like to get the following results showing that employee 1111 has
taken all required courses, employee 2222 has taken one but is missing one
and employee 3333 is missing all courses.

EmployeeID, CourseCode, CourseName, CompletedDate
1111 EDU01 Math 1/1/2006
1111 EDU02 Physics 1/1/2006
2222 EDU01 Math 1/1/2006
2222 EDU02 Physics NULL
3333 EDU01 Math NULL
3333 EDU02 Physics NULL

I suspect I need some sort of left or right join, but I've not been able to
get what I'm looking for. I'm a novice when it comes to queries. Thanks for
any help.