Trouble with a Select query

[KrisUK]

Hi,
I'm trying to work out a way to find the following info from a table called TRAINING:

EMP_REF COURSE_REF
1 1
1 2
1 3
1 4
2 1
2 2
2 3 etc

From this data I need to find which employees have taken a course, and which ones haven't.

First is obviously easy

select EMP_REF from TRAINING where COURSE_REF=1

Finding out which employees haven't done a certain course im finding a little tricky.
Ive tried

select EMP_REF from TRAINING where COURSE_REF!=1

but obviously because each employee does many courses this returns all the other courses so that wont work.

Ive tried a few other ways but cant get my head around it!!

Any help or suggestions would be great.
Im using BCB6.0 and IBX 6.08 and Firebird 1.5

TIA,
Kris

 

[unclejimbob]

One way would be to use the not exists predicate and issue something like this:

select e.EMP_REF from EMP e where not exists (select * from TRAINING t where e.EMP_REF = t.EMP_REF and t.COURSE_REF = 1)

I am assuming in this case that training is merely a convenient table that links the EMP and COURSE tables together in a particular relationship.

Or you can go with a variation like this:

select distinct t1.EMP_REF from TRAINING t1 where not exists (select * from TRAINING t2
where t2.EMP_REF = t1.EMP_REF and t2.COURSE_REF = 1)

Or there may be other similar answers...

hth
ujb

 

[KrisUK]

Hi ujb,
Thanks for the quick reply!! The first option worked like a charm I used

select E.EMP_REF,E.FORENAME,E.SURNAME from EMPLOYEE_DETAILS E where not exists
(select * from ONE_OFF_TRAINING t where e.EMP_REF = t.EMP_REF and t.ONE_OFF_COURSE_TITLE=:ONE_OFF_COURSE_TITLE) ORDER BY E.SURNAME")

Thanks Again,
Kris