Left join help needed

I'm try to create a left join query that will show employees who are NOT assigned to a given department.

I have an Employees table with the following cols:
id
first_name
last_name

I have an EmpDepts table with the following cols:
id
emp_id
dept_id

And finally, I have a Departments table with the following cols:
id
dept_name

This allows me to associate more than one department with one employee simply by having as many EmpDepts records with the emp_id and dept_id cols set with the correct id's.

I have a form where I can assign a dept to an employee. I have a list of employees on the left side, the departments list in the center and on the right, a list of employees assigned to the selected department.

When I select a department from the center list (maintenance dept for example) the right list shows employees assigned to that dept and now I'm try to create a query that will show employees on the left side list who are NOT assigned to the department 'maintenance'.

I have the following query but it is not working for the left side employees remaining that could be assigned to the target department. I see employees in this list that have already been assigned to the selected department :(


SELECT E.id, E.last_name FROM Employees E LEFT JOIN EmpDepts ED ON E.id=ED.emp_id AND ED.emp_id <> E.id

I'm sorry, but that query makes no sense.

ON E.id=ED.emp_id AND ED.emp_id <> E.id

???? That is an *IMPOSSIBLE* condition. It will *ALWAYS* be FALSE.

Because you are asking for ED.emp_id to *BOTH* be EQUAL *AND* NOT EQUAL to E.id.

So there will never be any matches in the EmpDepts table.

As to why you then get *ALL* employees:

By definition, a LEFT JOIN will show you all the records in the left-side table. That is, in your query, all the records in the EMPLOYEES table.

That's the whole reason to use a LEFT JOIN: You *WANT* all the records from the left side table, whether or not there is a matching record in the right side table.

*THIS* is what you need, I think:

SELECT E.id, E.last_name
FROM Employees E LEFT JOIN EmpDepts ED
ON E.id = ED.emp_id
WHERE ED.emp_id IS NULL

The problem is, the EmpDepts table may contain only one record (one employee assigned to one dept) but I want to display ALL the employees (from the left table) that isn't assigned to that one dept, but because I'm joining these two tables, I'm only getting the employee records that match the employees and empdept table ID's.

I have to rethink this.

Okay, minor change:

SELECT E.id, E.last_name
FROM Employees E LEFT JOIN EmpDepts ED
ON E.id = ED.emp_id AND ED.dept_id = 17
WHERE ED.emp_id IS NULL

Where I'm guessing about the name of the field (dept_id) and giving you a dummy value (17).

Adjust to fit.

That worked! You're a genius!

Thanks...