• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

SQL Join Problem - Show Records where count (x) is null

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,
                      DHHRBinfo.dbo.HRInfo.LastName, DHHRBinfo.dbo.HRInfo.FirstName
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.
0
dhadmin
Asked:
dhadmin
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can use a Left outer join
SELECT a.EmployeeID, ISNULL(AuditCount , 0 ) as AuditCount
FROM HRInfo  a
LEFT JOIN  (
     SELECT EmployeeID , COUNT(EmployeeID ) as AuditCount
     FROM dbo.SafetyAudits
     GROUP BY EmployeeID
) B  on a.EmployeeID = b.EmployeeID

0
 
dhadminAuthor Commented:
That general idea worked, thanks.  Some typos and logic errors in the above, so here's a copy of what actually worked.


SELECT     ISNULL(SafetyAudits.AuditCount, 0) AS AuditCount, DHHRBinfo.dbo.HRInfo.FirstName, DHHRBinfo.dbo.HRInfo.LastName
FROM         DHHRBinfo.dbo.HRInfo LEFT OUTER JOIN
                          (SELECT     EmployeeID, COUNT(UniqueAuditNo) AS AuditCount
                            FROM          dbo.SafetyAudits AS SafetyAudits_1
                            GROUP BY EmployeeID) AS SafetyAudits ON DHHRBinfo.dbo.HRInfo.EmployeeId = SafetyAudits.EmployeeID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now