Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following table :

employee_id int,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_number varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salary float,
commission_pct float,
manager_id int,
department_id int,
constraint emp_pk primary key(employee_id)

I have tried this :

SELECT * FROM employees e1
WHERE e1.employee_id = ( SELECT employee_id
                           FROM employees e2
          WHERE e2.department_id = e1.department_id 
          AND e2.salary = e1.salary);

What is wrong?

4 Answers 4


This should work:

select e1.* 
  from employees e1 inner join (
    select count(employee_id) as CNT, department_id, salary 
    from employees group by departmant_id, salary) t1 
  on e1.department_id=t1.department_id and e1.salary=t1.salary 
  where t1.CNT>1

The problem with your own query is twofold:

  • the return value of (select...from ..e2..) is from the very nature of your problem not unique, so the "=" relationship should either throw an error or return whichever is the first result (I'm not familiar enough with MySQL to know which applies), which doesn't have to be the one you're looking for.

  • filtering via "e1.employee_id =" will only ever be true for the record itself, but NOT for another record that matches your criteria

If you insist on using a filter:

SELECT * FROM employees e1
WHERE (e1.department_id,e1.salary) IN 
 (SELECT e2.department_id,e2.salary
  FROM employees e2
  WHERE e2.employee_id <> e1.employee_id)
  • You can use WHERE (department_id, salary) in (SELECT e2.department_id, e2.salary FROM ...) in MySQL if you want, no need for concat(). Commented Mar 18, 2014 at 11:19
  • Oh, good to know, thanks. (Like I said, MySQL specifics are not really my forte :)). So using a filter should be more plausible in MySQL then. I'd still recommend the join though. Commented Mar 18, 2014 at 13:46
  • I guess you usually work with SQL-Server because that's standard SQL actually (that SQL-Server hasn't implemented yet.) Commented Mar 18, 2014 at 13:48
  • You guess correctly :) Commented Mar 18, 2014 at 13:49
select employee_id, first_name,department_id, salary
from employees 
where ( department_id , salary ) IN 
    select department_id, salary 
    from employees
    group by department_id, salary
    having count(*) > 1
order by department_id , salary,employee_id;

You can find common records from single table with the following code:

select emp_id, emp_name, sal from tbl_emp where emp_name in 
(select emp_name from tbl_emp
group by  emp_name
having COUNT(*) > 1)
select department_id, salary, count(employee_id) as employees
    from employees
    group by department_id, salary
    having count(*)>1
    order by department_id, salary
  • A little explanation would probably help a bit the original poster...
    – joanolo
    Commented Apr 3, 2017 at 6:38
  • 1
    @joanolo Doesn't even answer the original question. Commented Apr 3, 2017 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.