By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

sql statement help

P: n/a
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.

Dec 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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');

Dec 18 '06 #2

P: n/a
Beautiful! You're gold Jerry, gold!

Dec 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.