Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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)
share|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 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 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 at 13:48
    
You guess correctly :) –  Sascha Rambeaud Mar 18 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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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