Need help with join

I have a query that isn't working the way I think it should:


SELECT E.last_name,
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.

this is what you want:
6 are not assigned to any departments

this is how to get it:

SELECT E.last_name
FROM Employees E
LEFT JOIN EmpDepts ED ON E.id = ED.emp_id
WHERE ED.emp_id is null and E.company_index = 63


you don't need to count as count is 0 anyway.

@BubikolRamios

Thanks...!!!

*** CAUTION ***

That query works in this particular instance, but it will fail in most other situations.

When you use a condition in the WHERE clause that refers to the *dependent* table in an outer join (that is, the RIGHT table in a LEFT JOIN, for example), you just converted that outer join into an INNER join!!!

The sole exception to this rule is when the condition you are testing is IS NULL (or IS NOT NULL) as Bubikol is doing here.

For other conditions, you *MUST* move the condition on the dependent table to the ON section of the query, thus (just an example, not applicable to this case):
SELECT E.last_name
FROM Employees E
LEFT JOIN EmpDepts ED
ON E.id = ED.emp_id AND ED.dept_no <> 17
WHERE E.company_index = 63

If the part of the condition there in red were moved to the WHERE, you would no longer have a LEFT JOIN.

Again, not relevant in this case, but a fair warning for the future.