OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Get the job

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Get the job [message #371180]

Wed, 13 September 2000 05:42

Mahesh Pednekar
Messages: 28
Registered: August 2000

Junior Member

I have a table emp which has following fields:-
empno,ename,job,deptno

How to get the job which belongs to all departments,
Without using UNION ?

regards.
Mahesh.

Report message to a moderator

Re: Get the job [message #371188 is a reply to message #371180]

Wed, 13 September 2000 09:56

Bala
Messages: 205
Registered: November 1999

Senior Member

This would do that,

select a.job from
(select count(*) cnt, job from emp
group by job) a,
(select count(distinct deptno) cnt1 from emp
) b
where a.cnt = b.cnt1
group by a.job
/

Try it

Bala

Report message to a moderator

Re: Get the job [message #371191 is a reply to message #371180]

Wed, 13 September 2000 23:04

sriram
Messages: 58
Registered: September 2000

Member

Dear Friend,
Try this and reply me ..

select job from (select job from (select deptno,job from emp group by deptno,job) x) group by job having count(job) > 1;

Have a nice day
Sriram

Report message to a moderator

Re: Get the job [message #371193 is a reply to message #371191]

Wed, 13 September 2000 23:41

sunil gupta
Messages: 11
Registered: September 2000

Junior Member

hi friend
this query will return jobs in one or more dept
instead u can try out using

select job from
(select job,count(distinct deptno) from emp
group by job
having count(distinct deptno) >=
(select count(distinct deptno) from emp)
)

Report message to a moderator

Previous Topic:	UtPLSQL Project for Developers
Next Topic:	Database size?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 08:20:58 CDT 2025