| 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.
|
|
|
|
| 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
|
|
|
|
| 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
|
|
|
|
| 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)
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
