comparing some values at the table-CodePudding

empno	ename	job	mgr	hiredate	sal	deptno
7839	KING	PRESIDENT		2031-11-17	5000.00	10
7934	MILLER	CLERK	7782	2032-01-23	1300.00	10
7782	CLARK	MANAGER	7839	2031-06-09	2450.00	10
7369	SMITH	CLERK	7902	2030-12-17	800.00	20
7876	ADAMS	CLERK	7788	2037-05-23	1100.00	20
7566	JONES	MANAGER	7839	2031-04-02	2975.00	20
7902	FORD	ANALYST	7566	2031-12-03	3000.00	20
7788	SCOTT	ANALYST	7566	2037-04-19	2900.00	20
7521	WARD	SALESMAN	7698	2031-02-22	1250.00	30
7844	TURNER	SALESMAN	7698	2031-09-08	1500.00	30
7900	JAMES	CLERK	7698	2031-12-03	950.00	30
7499	ALLEN	SALESMAN	7698	2031-02-20	1600.00	30
7698	BLAKE	MANAGER	7839	2031-05-01	2850.00	30
7654	MARTIN	SALESMAN	7698	2031-09-28	1250.00	30

so i have this table about that have data of the employee. what i want to ask is how to get someone who has bigger sal than the lowest sal manager that has the same deptno with him/her. and the job is not president or manager.

for example at the deptno number 20 the lowest sal manager is supposed to be jones with 2975. the one who has bigger sal than jones and have the same deptno with jones is ford so i want to print this out. i already made my sql and i write it like this

select ename,sal from emp

where sal >= all(select min(sal) from emp where job = 'MANAGER' group by deptno)

and job not like 'PRESIDENT'

and job not like 'MANAGER'

group by deptno, ename,sal;

actually this kind of sql is work pretty well and can print ford out but when i look it again i notice that when i used all it just compare with all the data that took out with the where clause and not compare it by group by deptno number. is there any way to compare the data group by deptno?

CodePudding user response：

You need to add a WHERE condition, e1.deptno = e2.deptno, that connects your sub query with the table.

SELECT e1.ename, e1.sal
FROM emp e1
WHERE sal >= (SELECT min(sal) FROM emp e2 WHERE e2.job = 'MANAGER' and e1.deptno = e2.deptno)
AND e1.job not like 'PRESIDENT'
AND e1.job not like 'MANAGER'
GROUP BY e1.deptno, e1.ename, e1.sal;

Kindly accept my answer if it works. Thanks!

CodePudding user response：

Ok here's a way to do this. Notice that slightly modified your query into a join instead of subquery

select e1.*
from emp e1
join 
(select deptno, min(sal) as minsal from emp where job = 'MANAGER' group by deptno) e
on e1.sal>=e.minsal and e1.deptno=e.deptno
AND e1.job not like 'PRESIDENT'
AND e1.job not like 'MANAGER'