SQL Subqueries (error 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 sub-query 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.

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 = ... );

CodePudding user response：

You can JOIN the two tables (rather than using a CROSS JOIN) and then use an analytic COUNT function to count the number of people with the last_name of Zlotkey in each department and then filter to only show the details of those departments which do:

SELECT last_name,
       hire_date,
       department_name
FROM   (
  SELECT e.last_name,
         e.hire_date,
         d.department_name,
         COUNT(CASE e.last_name WHEN 'Zlotkey' THEN 1 END) OVER (
           PARTITION BY d.department_id
         ) AS has_zlotkey
  FROM   employees e
         INNER JOIN departments d
         ON (e.department_id = d.department_id)
)
WHERE  has_zlotkey > 0;

Alternatively, you can use EXISTS:

SELECT e.last_name,
       e.hire_date,
       d.department_name
FROM   employees e
       INNER JOIN departments d
       ON (e.department_id = d.department_id)
WHERE  EXISTS (
         SELECT 1
         FROM   employees x
         WHERE  last_name = 'Zlotkey'
         AND    e.department_id = x.department_id
       )

or IN:

SELECT e.last_name,
       e.hire_date,
       d.department_name
FROM   employees e
       INNER JOIN departments d
       ON (e.department_id = d.department_id)
WHERE  e.department_id IN (
         SELECT department_id
         FROM   employees
         WHERE  last_name = 'Zlotkey'
       )