We help IT Professionals succeed at work.

Changing the WHERE clause does not return what I am expecting

dyarosh asked
Last Modified: 2012-05-06
With the help of Experts I finally got the following query working where it returned 1 record for each employer with a count of active employees, paidout employees and forfeiture employees.  Data set returned contains 3 rows with 5 columns.  So far so good until I discovered that my query for active employees was incorrect.  In the current query, the WHERE clause is Employees.EmployeeStatus='ACTIVE'.  I need to change the query to be WHERE Employees.PlanStatus<>'PAID OUT' AND Employees.PlanStatus<>'FORFEITURE'.  I thought this would be a simple change but when I made it, the data set returned contains 5 rows with 5 columns.  The one employer has an employee with a PlanStatus = "PAID OUT" and another Employee with a PlanStatus = "FORFEITURE".  As soon as I change the = to <> or add the second condition to the WHERE clause, more than one record is returned for the employer that has employees with different plan status.  Any insight into this problem would be appreciated.

SELECT a.EmployerName, a.EmployerID, b.ActiveEmployees, c.PaidOutEmployees, d.ForfeitureEmployees
FROM ((employers AS a LEFT JOIN (SELECT Count(Employees.EmployeeStatus) AS ActiveEmployees, Employees.EmployerID FROM Employees WHERE Employees.EmployeeStatus='ACTIVE' GROUP BY Employees.EmployerID, Employees.EmployeeStatus)  AS b ON a.EmployerID=b.EmployerID) LEFT JOIN (SELECT Count(Employees.PlanStatus) AS PaidOutEmployees, Employees.EmployerID FROM Employees WHERE Employees.PlanStatus='PAID OUT' GROUP BY Employees.EmployerID, Employees.PlanStatus)  AS c ON b.EmployerID=c.EmployerID) LEFT JOIN (SELECT Count(Employees.PlanStatus) AS ForfeitureEmployees, Employees.EmployerID FROM Employees WHERE Employees.PlanStatus='FORFEITURE' GROUP BY Employees.EmployerID, Employees.PlanStatus)  AS d ON c.EmployerID=d.EmployerID;

Open in new window

Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
Personally, I find this type of left outer join nesting confusing and hard to maintain. Try figuring this out a month from now when you've moved onto other things.

Of course, when writing Sql, there are many factors to consider, especially the amount of data and frequency fo the query. In other words, you should always evaluate (and re-evaluate as data and load increases) approaches to make sure they're acceptably efficient.

I'd simplify the query using subqueries, which makes it super easy to tweak them individually without harming your eyeballs.
SELECT  c.EmployerName,
        ActiveEmployees = (SELECT count(*) FROM Employees WHERE EmployerID = c.EmployerID and PlanStatus != 'FORFEITURE' and PlanStatus != 'PAID OUT'),
        PaidOutEmployees = (SELECT count(*) FROM Employees WHERE EmployerID = c.EmployerID and PlanStatus == 'PAID OUT'),
        ForfeitureEmployees = (SELECT count(*) FROM Employees WHERE EmployerID = c.EmployerID and PlanStatus == 'FORFEITURE')
FROM    employers c

Open in new window

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.