select query help 1

[LloydDobler]

Hi everyone, i'm trying to get this query to work. What I need it to do is give me all the employees who have either been trained on a machine (dateofcompletion), or have worked on a machine (PRDDate). All I seem to be getting is employees who have done both, which is okay, but I need those who have one and not the other to be in the results as well. Here's my code. Any help would be great. Thanks

SELECT I.EmployeeNumber, I.MachineNumber, I.PRDdate AS LastRunByEmployee, dbo.trainingtable.DateofCompletion AS LastTrained

FROM dbo.EmployeesTable E INNER JOIN PRD.dbo.tbl_Production I ON E.EmployeeNumber = I.EmployeeNumber INNER JOIN dbo.trainingtable ON E.EmployeeNumber = dbo.trainingtable.EmployeeNumber AND I.MachineNumber = dbo.trainingtable.MachineNumber

WHERE (I.PRDdate =(SELECT MAX(PRDdate) FROM PRD.dbo.tbl_Production))
OR (dbo.trainingtable.DateofCompletion <>
(SELECT MAX(dateofcompletion) FROM dbo.trainingtable))

 

[iker3000]

Hi!

Change the to inner joins to LEFT OUTER JOINs (I assume, that there is no row in tbl_Production for Employees who have not worked and, there is no rows in trainingtable for Employees who have not been trained).

Iker

 

[LloydDobler]

This doesn't seem to work, and I tried RIGHT OUTER JOIN as well. And you are right, there are no rows in either table if they have not worked or not been trained respectively.

 

[LloydDobler]

I think I've got it now: One inner join, and one outer. This gives me what I'm looking for. Iker, thanks for steering me in the right direction. Have a star!

SELECT TOP 100 PERCENT dbo.trainingtable.DateofCompletion AS LastTrained, dbo.trainingtable.EmployeeNumber, dbo.trainingtable.MachineNumber,
dbo.trainingtable.TrainingModule, I.PRDdate AS LastRunOn
FROM dbo.EmployeesTable INNER JOIN
dbo.trainingtable ON dbo.EmployeesTable.EmployeeNumber = dbo.trainingtable.EmployeeNumber LEFT OUTER JOIN
PRD.dbo.tbl_Production I ON dbo.trainingtable.MachineNumber = I.MachineNumber AND
dbo.EmployeesTable.EmployeeNumber = I.EmployeeNumber
WHERE (dbo.trainingtable.TrainingModule = N'machine specific training') AND (dbo.trainingtable.DateofCompletion <>
(SELECT MAX(dateofcompletion)
FROM dbo.trainingtable)) OR
(dbo.trainingtable.TrainingModule = N'machine specific training') AND (I.PRDID <>
(SELECT MAX(prdID)
FROM PRD.dbo.tbl_Production))
ORDER BY dbo.EmployeesTable.EmployeeNumber, dbo.trainingtable.MachineNumber