Please help me write a mysql query to select members of a particular department

Let's say I have 2 tables, employee and department, department has 2 departments, dpt1 and dpt2, and开发者_运维知识库 there are 12 employees, 6 is each department, and 4 belonging to both departments. How can I use a mysql query to find those employees who belong only to dpt2 and not dpt1?

---

SELECT employee.*
FROM employee
INNER JOIN employee_belongs_to_departments
WHERE employee.id = employee_belongs_to_departments.employee_id
AND employee_belongs_to_departments.department_id = 'dpt2'
AND employee.id NOT IN (
  SELECT DISTINCT ebtd2.employee_id
  FROM employee_belongs_to_departments ebtd2
  WHERE ebtd2.employee_id = employee.id
  AND ebtd2.department_id = 'dpt1'
)

---

The usual approach in this kind of situation would be to have a third "connection" table, called something like employeeDepartment.

CREATE TABLE employeeDepartment (
    employee INT UNSIGNED NOT NULL,
    department INT UNSIGNED NOT NULL,
    PRIMARY KEY (employee, department)
)

and then you also make employee and department foreign keys to the primary keys of the appropriate tables, though this requires you to be using the right storage engine (not that that is hard to do).

This makes doing various things a whole lot easier than the approach you seem to be going for (storing a list of values in one column).

You could then use

SELECT
    eD1.employee
FROM
    employeeDepartment AS eD1
    LEFT JOIN employeeDepartment AS eD2 ON
        eD1.employee = eD2.employee AND
        eD2.department = 'dpt2' -- replace with appropriate integer
                                -- if using integers as I suggested
WHERE
    eD1.department = 'dpt1' AND -- same comment applies
    eD2.employee IS NULL