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:
I have an EmpDepts table with the following cols:
And finally, I have a Departments table with the following cols:
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