Need help with join

frank727 · 02-13-2013, 06:43 AM

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

PHP Code:


			
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:

PHP Code:


			
 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.

BubikolRamios · 02-13-2013, 10:53 AM

this is what you want:

Quote:

6 are not assigned to any departments

this is how to get it:

Code:

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.

frank727 · 02-13-2013, 07:07 PM

@BubikolRamios

Thanks...!!!

Old Pedant · 02-13-2013, 08:14 PM

*** 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):

Code:

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.