SQL Join Problem - Show Records where count (x) is null
Posted on 2009-05-07
I'm stuck on a query and I think the problem has to do with my join syntax. I have two tables...an HR name list with employee ID as a key and an audit table where employee ID is a foreign key. I want to write a query that lists all of the employees and their count of audits conducted in the last month, BUT I want to show zero for employees that have created no audits that month.
Currently my query looks like...
SELECT COUNT(dbo.SafetyAudits.UniqueAuditNo) AS AuditCount,
FROM DHHRBinfo.dbo.HRInfo FULL OUTER JOIN
dbo.SafetyAudits ON dbo.SafetyAudits.EmployeeID = DHHRBinfo.dbo.HRInfo.EmployeeId
WHERE (dbo.SafetyAudits.AuditDate > '5/1/09')
GROUP BY DHHRBinfo.dbo.HRInfo.LastName, DHHRBinfo.dbo.HRInfo.FirstName
and if I have 200 employees and 8 did audits in May, my output is only those 8 employees. I want it to be all 200 employees, with zero for the 192 that didn't do any audits.