CODEWITHSUNDEEP
sqljoinsubquery
Akash
Akash

Reputation: 425

SQL Problems : Error Code: 1242. Subquery returns more than 1 row

GOAL

I simply want to select the details of those employees having salary greater then the HCL(company-employe).

Tables

What I've tried :

SELECT Emp.emp_id, Emp.Emp_name, Department.dept_name, Emp.Salary, Company.company_name from emp 
 JOIN department ON(emp.emp_id=department.emp_id) 
  JOIN company ON(department.dept_id=company.dept_id) 
   where emp.salary > (SELECT Emp.Salary FROM emp where Company.company_name='HCL');

Problem :

When I'm executing this query it shows : Error Code: 1242. Subquery returns more than 1 row :

To remove the above error, I've tried :

SELECT Emp.emp_id, Emp.Emp_name, Department.dept_name, Emp.Salary, Company.company_name from emp 
     JOIN department ON(emp.emp_id=department.emp_id) 
      JOIN company ON(department.dept_id=company.dept_id) 
       where emp.salary > (SELECT Emp.Salary FROM emp where Company.company_name='HCL' LIMIT 1);

I know that by providing LIMIT to the query it doesn't provides the desired output but it removes the Subquery returns more than 1 row error and when i executed this query it provides blanks rows and column at the output.

So anyone can help me where and what i am lacking in the query to select the desired details..

Upvotes: 0

Views: 243

Answers (2)

GMB
GMB

Reputation: 222622

I think that you want:

select 
    e.emp_id, 
    e.emp_name, 
    d.dept_name, 
    e.salary, 
    c.company_name 
from emp e
inner join department d on e.emp_id = d.emp_id
inner join company c on d.dept_id = c.dept_id
where e.salary > (
    select max(e1.salary)
    from emp e1 
    inner join company c1 on c1.dept_id = e1.dept_id
    where c1.company_name = 'HCL'
);

This will give you the employees that have a salary greater than all salaries of company HCL.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

company.company_name doesn't look right, but some databases do support nested record structures in rows. I assume that your database does.

I would suggest that you want one of the following:

-- greater than the average
where emp.salary > (select avg(Emp.Salary)
                    from emp 
                    where Company.company_name = 'HCL'
                   );

-- greater than all
where emp.salary > (select max(Emp.Salary)
                    from emp 
                    where Company.company_name = 'HCL'
                   );

-- greater than any
where emp.salary > (select min(Emp.Salary)
                    from emp 
                    where Company.company_name = 'HCL'
                   );

Upvotes: 1

Related Questions