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?


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)
| improve this answer | |
  • You can use WHERE (department_id, salary) in (SELECT e2.department_id, e2.salary FROM ...) in MySQL if you want, no need for concat(). – ypercubeᵀᴹ Mar 18 '14 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. – Sascha Rambeaud Mar 18 '14 at 13:46
  • I guess you usually work with SQL-Server because that's standard SQL actually (that SQL-Server hasn't implemented yet.) – ypercubeᵀᴹ Mar 18 '14 at 13:48
  • You guess correctly :) – Sascha Rambeaud Mar 18 '14 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;
| improve this answer | |

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)
| improve this answer | |
select department_id, salary, count(employee_id) as employees
    from employees
    group by department_id, salary
    having count(*)>1
    order by department_id, salary
| improve this answer | |
  • A little explanation would probably help a bit the original poster... – joanolo Apr 3 '17 at 6:38
  • 1
    @joanolo Doesn't even answer the original question. – Colin 't Hart Apr 3 '17 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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