List the emps whose sal > his Manager but less than other Managers

List the emps whose sal > his Manager but salary less than other Managers

Table

emp_ID  emp_Name    emp_sal_K   emp_manager
1        Ali          200         3
2        Zaid         620         4
3        Mohd         1140        2
4        LILY         600         NULL
5        John         1240        6
6        Mike         1160        4
7        John         1240        6
8        Mohd         1640        2

Query

select *
from emp_demo2 e1
      where emp_sal_K > 
        (select emp_ID from emp_demo2 e2 
        where e1.emp_manager = e2.emp_ID and e2.emp_sal_K 
         < all
          (select emp_id from emp_demo2 e3
           where e2.emp_ID = e3.emp_id))

Result: null columns. There are 4 managers in total 2,4 ,6 and 3. Here clearly emp_id 2 Zaid is a person whose salary 620 is greater than his manager’s salary emp_id 4 which is 600 but less than all other managers emp_id 6 and 3. So I should get that result but getting nothing.

Expected result

emp_ID  emp_Name    emp_sal_K   emp_manager
  2      Zaid         620         4

Answer

SELECT emp.* 
FROM   emp_demo2 emp 
       LEFT JOIN emp_demo2 mgr 
              ON emp.emp_manager = mgr.emp_id 
WHERE  emp.emp_sal_k > mgr.emp_sal_k 
       AND emp.emp_sal_k < (SELECT Min(mgr1.emp_sal_k) 
                            FROM   emp_demo2 emp1 
                                   JOIN emp_demo2 mgr1 
                                     ON emp1.emp_manager = mgr1.emp_id 
                            WHERE  mgr1.emp_id <> emp.emp_manager 
                                   AND mgr1.emp_id <> emp.emp_id)