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?
Best Answer
This should work:
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: