Last edited by Steven Low; 04-05-2007 at 6:49 PM. Reason: Resolved
you need to understand why this query doesnt work.. you get a list of all employees where they earn less than 3000, regardless of their department, and then you get all the distinct department names out of that. If you have 20 employees in 5 departments, earning alternately 2000 and 4000, then your where clause selects only the £2000 employees from each department, then boils down the names.. do you see why this wont work? we are doing things in the wrong order.. cull then group, should be group then cull.
do you mean you want to select the department names with employees having a max salary of less than 3000 (by department)?
oh look.. there's the query
Thanks for that hint
I am getting one problem now which is some employees havent got a salery and its not showing those departments. I have to state is null but i am not sure were i need to state it.
Heres what ive done. So far
select dept.deptno, dname, loc
from emp, dept
where dept.deptno = emp.deptno
GROUP BY dept.deptno, dname, loc
Having MAX(emp.sal) <= '3000'
order by dept.deptno
try to use modern ANSI JOIN (INNER/OUTER/CROSS/EQUI/NATURAL) syntax, rather than the old style FROM x,y,z WHERE syntax.. its much less flexible because it can technically only do inner joins
for those departments with only employees whose salary is null you need to sub the nulls for something.. either a really big or a really small number depending on what logic you need..Code:select dept.deptno, dname, loc from emp INNER JOIN dept ON dept.deptno = emp.deptno GROUP BY dept.deptno, dname, loc Having MAX(NVL(emp.sal,9999999)) <= '3000' order by dept.deptno
the query above is an example of that
ps, I cant believe I wrote:
HAVING MAX(...) <= '3000'
MAX, as a function, returns a number.. Using it in combination with a string can cause the number to be promoted to a string, so '10000' WILL BE <= '3000'
Never use strings in SQL when you should use numbers... Cant believe I let that one slip without a comment..
Last edited by Steven Low; 04-09-2007 at 6:26 AM. Reason: Resolved