I have a query that isn't working the way I think it should:
COUNT(ED.id) AS dcnt FROM Employees E
LEFT JOIN EmpDepts ED ON E.id = ED.emp_id
WHERE E.company_index = 63
GROUP BY E.last_name HAVING dcnt = 0
My EmpDepts (employee departments) table structure looks like this:
id company_index emp_id dept_id
882 63 1064 479
883 63 1064 477
If an employee has been assigned to a Department, in the EmpDepts table, a record is created that shows the employee id and the associated department table id.
If no department is assigned to an employee, there will be no record in the EmpDepts table for that employee record id.
That top query that isn't working should be returning 6 records out of my 200 records because 6 are not assigned to any departments, but my query is returning only 2 records, thus, missing other 4 who have not been assigned to any departments.
I've checked the record structure of the 2 records that are being 'caught' by the top query vs the 4 that are not being caught and I can't see any differences. I think the main thing about that query is that I've manually verified that they are not in the EmpDepts table...so I'm puzzled as to why the query doesn't catch all 6 records.
Thanks for any help.