CODEWITHSUNDEEP
sqloracle-database
Zahid Khan
Zahid Khan

Reputation: 3239

Sub-query results 'Single Row Sub-Query results more than One Row'

I have a Table Office created in SQL Terminal on Oracle 11g .

Whose 3 columns are
Ename | Bname | salary where:-
Ename : The name of the employer.
Bname : Boss Name
Salary : Employee salary.
The values inside the Office Table are :-

ENAME                BNAME                    SALARY
-------------------- -------------------- ----------
Zahid                Muheet                    30000
Arif                 Muheet                    20002
Ahtishaam            Muheet                    20002
Ayaaz                Muheet                     2000
Zaid                 Muheet                    40000
Muheet               Akib                      40000

6 rows selected.

I want to retrieve all those employees name(ename) whose salary is greater or equal to his boss salary.

Output Should be:

ENAME
------
Zaid

I'm writing a query on my terminal 

select emp.ename
from office emp
where emp.salary >= (select a.salary
                     from office a,
                          office b
                     where a.ename = b.bname)

I guess it should work but its resulting in error :

ORA-01427: single-row subquery returns more than one row

Upvotes: 0

Views: 63

Answers (4)

xQbert
xQbert

Reputation: 35333

And a 3rd approach using exists and a correlated subquery.

SELECT Emps.EName
FROM Office Emps
WHERE EXISTS (SELECT * 
              FROM Office Boss 
              WHERE Emps.BName = Boss.EName 
                and Emps.Salary >= Boss.Salary)

Upvotes: 0

Magisch
Magisch

Reputation: 7352

JOIN the table with itself:

SELECT
oa.ename
FROM office AS oa
INNER JOIN office AS ob ON oa.bname = ob.ename
WHERE oa.salary >= ob.salary;

This creates a result set where everyone's salary and name and their bosses salary and name are available, and then filters it by those where the employee salary is greater then or equal to the boss's salary.

In a case such as this one there is no need to use a subquery, but if you absolutely have to, there is a way using a subquery as well:

SELECT
oa.ename
FROM office AS oa
WHERE oa.salary >= (SELECT ob.salary FROM office AS ob WHERE ob.ename = oa.bname)

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

You can use a self-join for this as well

select o1.ename 
from office o1
join office o2 on o1.ename = o2.bname
where o1.salary >= o2.salary

Upvotes: 1

nacho
nacho

Reputation: 5397

select emp.ename 
from office emp 
where emp.salary>= ( select boss.salary 
                     from office boss 
                     where emp.bname = boss.ename)

Upvotes: 2

Related Questions