Query to Determine if Course is Taken

[Cydney]

Hi, I have a table that lists courses an employee has taken. I also have a
list of courses, when then expire and what their retraining course should be.
Now I need to determine if the employee has taken not only the original
course, but the retrained course when they're on separate lines.

So.. if he's taken course 123 which expires in 12 months. Then he takes
course 124. It shows in the list of courses that he's taken that 124 was also
completed. However, my flag just determines that 123 is expired. I need that
flag to not show if 124 has been taken.

Hope that's a complete enough explanation...

 

[Tom van Stiphout]

On Fri, 22 Feb 2008 16:24:00 -0800, Cydney

This sounds like a Union query. In English:
select all active courses taken
plus [this becomes your Union statement]
select all inactive courses taken where there is no retraining course
in the first set. [use: ... where not in (select <the_first_set>)]

The exact statement depends on the exact tables, and probably also on
a bit more exact definition of the problem. Hopefully the above will
get you started.

-Tom.

 

[Ken Sheridan]

If I understand you correctly you want to return rows where no other row
exists for that employee where the course taken is the retaining course
specified in the first row. The solution to this type of problem is to
restrict the rows returned by a query by means of a correlated subquery.
Lets assume your table is called CoursesTaken and has, amongst others,
columns EmployeeID, CourseTakenID, RetrainingCourseID. The first column
would reference the primary key of an Employees table and the other two
columns the primary key of a Courses table. The CoursesTaken table in fact
models a many-to-many relationship type between the other two. So a query
would be like this:

SELECT *
FROM CoursesTaken AS CT1
WHERE NOT EXISTS
(SELECT *
FROM CoursesTaken AS CT2
WHERE CT2.EmployeeID = CT1.EmployeeID
AND CT2.CourseTakenID = CT1.RetrainingCourseID);

The above would return just those rows where an employee has not yet taken
the specified retraining course. An alternative solution would be to include
the subquery in the outer query's SELECT clause. This would return rows
showing all courses taken, but with an extra column showing the specified
retraining course only if taken; otherwise the column would be null:

SELECT EmployeeID, CourseTakenID,
(SELECT RetainingCourseID
FROM CoursesTaken AS CT2
WHERE CT2.EmployeeID = CT1.EmployeeID
AND CT2.CourseTakenID = CT1.RetrainingCourseID)
AS RetrainingCourseTaken
FROM CoursesTaken AS CT1;

The above queries use only the one table, but in reality of course (no pun
intended!) you'd join the Employees and Courses tables to the CoursesTaken
table in the outer query of the first query to show the employee's name and
the course title. In the case of the second query these tables would again
be joined to the CoursesTaken table in the outer query, but you'd also join
the Courses table to the CoursesTaken table in the subquery and return the
course title rather than the CourseID as RetrainingCourseTaken column.

Ken Sheridan
Stafford, England

 

[Cydney]

Thanks so much! This set me on the right track.

 

[Cydney]

Ken, I hope you're still available to help me on this today.. I spent all day
yesterday trying out different scenarios and thought I had it figured out.
Then I did another test and ...it (my theories) broke down...

Here's my latest that is a slight twist on what I asked you for before.

(My Code below) CT1 has all the courses the employee has taken. CT2 has the
list of all the REQUIRED courses for the appropriate WC (worker category) the
employee falls in. What I want to see is all the courses that he is required
to take within all the courses he HAS taken.

Basically I should see:

WC Empl ReqCourse CourseTaken

AB Joe 123 123
AB Joe 124
AB Joe 125 125

I want to know that Joe hasn't taken his 124 course yet.

Later, I'll then want to add in the retraining course and have the decision
made of IF Joe has taken 123 and also 125 (the retraining), then he shouldn't
be flagged. Also, if he hasn't taken 124 OR 126 (which he couldn't have taken
without the 124 prerequisite) then he should be flagged.

Then.. the final step is, if he's taken the prerequisite course, and the
retraining course, but the retraining has expired, Joe needs to have a red
flag. Otherwise, all is well.

WC Empl ReqCourse CourseTaken RetrainCourse DateTaken Expires

AB Joe 123 123 125
AB Joe 124 126
AB Joe 125 125

Here's the code I have so far.. and it isn't working correctly.

SELECT WC, Empl, CourseTkn
FROM tmpEmplCoursesTaken AS CT1
WHERE (((Exists (SELECT WC, ReqCourse
FROM qCourses as CT2
WHERE CT1.WC=CT2.WC))=False));


I am in deepest gratitude for your help, Ken.