mysql: sub-query return more than 1 rows

Question

I was trying to solve this question given below by using the subquery in MySQL but can't find a solution as it's showing subquery returns more than one row.

Problem Statement: Display the emp name with their salaries and their managers and managers salary.
Display those only employees whose managers were hired after them.

select w.ename employee, w.SAL woSal, m.ename manager, m.SAL mSal
from emp w, emp m
where w.mgr = m.empno and
(SELECT E1.ENAME  
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO and ( E1.hiredate<E2.hiredate));

It simply means that your sub-query needs more conditions in the where clause so that it returns one row. for any further clarifications please don't hesitate to ask me again — Ted at ORCL.Pro
– Ted at ORCL.Pro, Commented Sep 26, 2017 at 19:07
what is 'mgr' column contains. Please add the schema and sample data — Harshil Doshi
– Harshil Doshi, Commented Sep 26, 2017 at 19:27

Mureinik · Accepted Answer · 2017-09-26 19:08:49Z

2

You don't need a subquery - just add the condition on the hiredate to the join condition. Also note that implicit joins (using more than one table in the from clause) is considered deprecated, and you'd probably be better off rewriting it as an explicit join:

SELECT w.ename employee, w.SAL woSal, m.ename manager, m.SAL mSal
FROM   emp w
JOIN   emp m
WHERE  w.mgr = m.empno and w.hiredate < m.hiredate

answered Sep 26, 2017 at 19:08

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql: sub-query return more than 1 rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related