SQL query where Employee works in more than one Department

Question

I have employee table with emp id (emp_id) and department (dep_id) fields. An employee could be working in more than one Department. I want to write a sql query to display unique emp_ids who work in more than one department.

Pl help me to write sql query.

Thx

Answer 1

Answered here: SQL query for finding records where count > 1 You need to use count, group by and having like this.

select emp_id, count(dep_id)
from employee_department
group by emp_id
having count(dep_id)>1

Answer 2

Query

SELECT COUNT(*)
  FROM
(
  SELECT id_employee, COUNT(*) AS CNT
    FROM Department_Employee
GROUP BY id_employee
) AS T
WHERE CNT > 1