Complex join

I have a C# application for tracking training. When I need to get the employee's missed training (exception report) it works fine on a one-by-one basis. Our HR folks can't sit and spin through 100s of screens, so I want to provide a comprehensive report for all employees.

Here is the first hack which places everything into a temporary table. Problem is I can't get a while loop to work with it as is. All three "sub queries" build the exception (missing) topics into the temp table.

I can loop in my application, but it makes the user click the print button for each report. I'm trying to get one report for all employees and all exceptions, breaking on employeeID. I figure they will run this quarterly to make sure everyone has their required training.

Any help is greatly appreciated. I didn't find anything in my ref books to help.

_E

DECLARE @EmployeeID int
DECLARE @MaxCount int

-- example only, real count will be +/- 200 and = to the number of active employees
set @MaxCount = 10

-- would like to exclude inactive employees
SET @EmployeeID =1

-- can use the employee table as input/limit
WHILE @EmployeeID < (select employeeID from employee)

DECLARE @MyTable TABLE (TopicID INT)
INSERT INTO @MyTable

-- topics based on primary role
SELECT rt.TopicID FROM ROLETOPICS rt
LEFT OUTER JOIN Employee e ON e.EmployeeID = @EmployeeID
WHERE rt.RoleID = e.PrimaryRoleID

UNION

-- optional topcs
SELECT TopicID
FROM EmployeeTopics et
WHERE et.EmployeeID = @EmployeeID

-- required topics (all employees)
UNION
SELECT TopicID
FROM Topic t
WHERE t.CategoryID = 3

SELECT mt.TopicID, Topic FROM @MyTable mt
INNER JOIN Topic t ON mt.TopicID = t.TopicID
WHERE mt.TopicID NOT IN(SELECT tr.TopicID FROM Training tr WHERE tr.EmployeeID = @EmployeeID)

--increment counters

I know I've left out some counter and variable initializers, but I can't get the basis loop to work at all for more than one record and that's specifice to an EmployeeID.