query help

[Steven Low]

Resolved
Resolved

 

[cjard]

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

 

[Steven Low]

Hi

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

 

[cjard]

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

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

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..

the query above is an example of that

 

[Steven Low]

Resolved

Resolved,
Resolved,
Resolved

 

[cjard]

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..

 

[Steven Low]

trigger help with delete

Resolved
Resolved

 

[cjard]

Yeah, um.. when stuff is resolved, we dont usually edit the contents of all the posts to "resolved" - someone else might have the same problem as you and want to read this discussion..