sql statement help

[gcoene]

I have 2 tables:
1) employee - with a unique primary key - employeeid
2) training - with an autonumber primary key, second field employeeid,
third field training

data in employee table:
001
002
003
004
005
....

data in training table
1 001 CPR
2 001 WHMIS
3 002 CPR
4 004 CPR
5 005 WHMIS
....

Basically, I want to write an SQL statement that shows all of the
employees and whether or not they have taken CPR, for example:

001 CPR
002 CPR
003 null
004 CPR
005 null
....

Thanks for your help.

[pietlinden]

gc****@gmail.com wrote:

I have 2 tables:
1) employee - with a unique primary key - employeeid
2) training - with an autonumber primary key, second field employeeid,
third field training

data in employee table:
001
002
003
004
005
...

data in training table
1 001 CPR
2 001 WHMIS
3 002 CPR
4 004 CPR
5 005 WHMIS
...

Basically, I want to write an SQL statement that shows all of the
employees and whether or not they have taken CPR, for example:

001 CPR
002 CPR
003 null
004 CPR
005 null
...

Thanks for your help.

We did this in school, so I've done this absolutely to death. You're
missing a table. The standard setup is like this:
Student---(1,M)---Attendance---(M,1)---Course

and it looks like you only have the first two.

The solution is to create a sort of partial cartesian product between
Course and Student.

SELECT Course.*, Student.StudentID
FROM Course, Student
WHERE Course.CourseName='CPR';

will return a record for each (student,Course='CPR') combination. Now
subtract that result from the Students table, and you're gold.

SELECT Student.FirstName, Student.LastName
FROM Student
WHERE StudentID Not In (SELECT Student.StudentID
FROM Course, Student
WHERE Course.CourseName='CPR');

[gcoene]

Beautiful! You're gold Jerry, gold!