SQL Subqueries (reeor in the subquery section)-CodePudding

The question is Find last names, hire dates of employees who work in the same departments as Zlotkey does. To answer it I wrote the following code. But I get an error (ERROR :ORA-01427: single-row subquery returns more than one row)

SELECT last_name, hire_date, department_name
From employees, departments
WHERE department_name = (
select department_name
from employees, departments
where last_name = 'Zlotkey');

I appreciate it if you can let me know what is the problem with my code and how it should be.

Thank you.

Code that I tried and got an error:

SELECT last_name, hire_date, department_name
From employees, departments
WHERE department_name = (
select department_name
from employees, departments
where last_name = 'Zlotkey');

CodePudding user response：

Presuming there are ID fields for the employees and departments tables, try:

SELECT 
    last_name, 
    hire_date, 
    department_name
FROM employees
JOIN departments
ON employees.employee_id = departments.employee_id
AND departments.department_id IN (
    SELECT DISTINCT department_id 
    FROM departments
    JOIN employees
    ON employees.employee_id = departments.employee_id
    AND last_name = 'Zlotkey');

This handles cases where there is one or more employees with the last name Zlotkey.

If there is more than one Zlotkey and your criteria require a specific employee, you can use their specific ID:

SELECT
    last_name, 
    hire_date, 
    department_name
FROM employees
JOIN departments
ON employees.employee_id = departments.employee_id
AND departments.department_id IN (
 SELECT department_id
 FROM departments 
 WHERE employee_id = ... );