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.

You don't need a loop, not sure of the best way to do the mandatory training, but this is a starter for 10.

SELECT ReqdTraining.employeeId, ReqdTraining.TopicId

FROM(

SELECT e.employeeId, rt.TopicID

FROM ROLETOPICS rt

JOIN Employee e ON rt.RoleID = e.PrimaryRoleID

UNION

SELECT et.employeeid, TopicID

FROM EmployeeTopics et

UNION

-- required topics (all employees)

SELECT E.EmployeeId, T.TopicID

FROM Topic t

CROSS JOIN Employee E

WHERE t.CategoryID = 3) ReqdTraining

LEFT JOIN Training T ON T.TopicId = ReqdTraining.TopicId

AND T.EmployeeId = ReqdTraining.EmployeeId

WHERE T.TopicId IS NULL

|||

Thank you, SimonSa. Cross join. D'Oh! Guess I should stick to object code, my DBA skills need work (which I'll get in the MCSD program.)

_E