4
EMPNO   ENAME   JOB         MGR     HIREDATE    SAL     COMM    DEPTNO

7369    SMITH   CLERK       7902    2000-12-17  800     Null        20

7902    FORD    ANALYST     7566    1992-12-03  3000    Null        20

Hi guys, I have this type of table, and I need to show the employees who earn more than there managers? It would be easier if we had 2 tables, but I cannot imagine a code within one table. Any ideas?

funnydman
  • 9,083
  • 4
  • 40
  • 55
Azat Aleksanyan
  • 55
  • 1
  • 1
  • 7

3 Answers3

4
  • You can do "self-join" between the two tables. In the second table, EMPNO will be equal to the corresponding MGR value.
  • Use Where to filter out cases where salary of employee is higher than that of manager.

Try:

SELECT employee.*
FROM your_table_name AS employee
JOIN your_table_name AS manager ON manager.EMPNO = employee.MGR 
WHERE employee.SAL > manager.SAL

As @Strawberry suggested in comments that column comparisons can be done in the join condition itself (instead of using Where). You can do the following as well: 

SELECT employee.*
FROM your_table_name AS employee
JOIN your_table_name AS manager ON manager.EMPNO = employee.MGR AND 
                                   employee.SAL > manager.SAL

The advantage of this approach is that we have to switch from Inner Join to Left Join, changes required to the query would be lesser.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • Thank you very much for the response. I was trying to implement it for 2 days! And I have an additional question which I guess is about understanding the sql logic. I need to give the hierarchy of the company, could you advice something about it? – Azat Aleksanyan Oct 13 '18 at 14:03
  • @AzatAleksanyan that is a very complex problem, involving Recusive Hierarchical query. Check: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Madhur Bhaiya Oct 13 '18 at 14:07
  • @AzatAleksanyan I would suggest you to build hierarchy in application code (eg: PHP) instead – Madhur Bhaiya Oct 13 '18 at 14:09
  • For column comparisons, I prefer AND to WHERE – Strawberry Oct 13 '18 at 14:56
  • @Strawberry why so ? Is it because join will bring less data for where to filter ? I believe both would use indexes though. – Madhur Bhaiya Oct 13 '18 at 16:01
  • 1
    @madhurbhaiya I think because there's less to change when switching from inner join to left join – Strawberry Oct 13 '18 at 18:47
  • @Strawberry updated answer to include your suggestion as well :) – Madhur Bhaiya Oct 13 '18 at 18:56
0

you should use self join 

SELECT e1.EMPNO, e1.ENAME
FROM employee e1
JOIN employee mgr ON mgr.EMPNO = e1.MGR 
WHERE e1.SAL > mgr.SAL
Salman A
  • 262,204
  • 82
  • 430
  • 521
Manish Singh
  • 934
  • 1
  • 12
  • 27
0
select name as Employee 
    from Employee as t
    where salary > (select salary from Employee where id=t.ManagerId);
Joundill
  • 6,828
  • 12
  • 36
  • 50
VISHNU GUPTA
  • 1
  • While this code may provide a solution to the question, it's better to add context as to why/how it works. This can help future users learn, and apply that knowledge to their own code. You are also likely to have positive feedback from users in the form of upvotes, when the code is explained. – borchvm May 04 '20 at 06:33