Retrieve employees who are working in more than 1 department

Arun Raj · Posted: Tue Apr 24, 2007 12:19 pm

Hi

I have an EMPL table with columns as

EMPNO, EMPNAME, DEPT1, DEPT2, DEPT3

I want to retrieve those employees who are working in more than 1 department.

Thanks
Arun

ashwinreddy · Posted: Tue Apr 24, 2007 2:29 pm

Hi,

Their might be many solutions i think one which is easer for me

Select EMPNO,EMPNAME

From EMPL

where (DEPT1 > 0
and DEPT2 > 0)

or

(DEPT2 > 0
and DEPT3 > 0)

or

(DEPT3 > 0
and DEPT1 > 0)

i have given a solution without testing, you have to test it.

And more optimised quires will be posted by other members.

Cheers
Ashwin kumar

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

Hi Ashwin,
I think it won't work....

Arun pls go through this link
www.ibmmainframes.com/viewtopic.php?t=14746&highlight=

vicky10001 · Active User Joined: 13 Jul 2005 Posts: 136

Dear All,

Please give proper answer bocoz its important.
Pleas find the above two answer was wrong.

Arun Raj · Posted: Tue Apr 24, 2007 5:49 pm

Hi mahi

Yours ws a different scenario where you were having only two columns EMPNO and DEPT.But in my case it is not so.

Thanks
Arun

dick scherrer · Posted: Tue Apr 24, 2007 8:47 pm

Hello,

The previous dialog has me confused

If i read this

Arun Raj · Posted: Wed Apr 25, 2007 11:08 am

Hi all,

Thanks for the response

Arun

dick scherrer · Posted: Wed Apr 25, 2007 11:35 pm

Hello Arun,

You're welcome

But. . . . did you get an answer that works for you?

If you did, and post it, it may help someone later with a similar question.

Raphael Bacay · Posted: Fri May 18, 2007 1:23 pm

Hi!

Maybe instead of 0 you can use null. The SELECT will look something like this:

SELECT EMPNO, EMPNAME
FROM EMP
WHERE (DEPT1 NOT NULL AND
DEPT2 NOT NULL) OR
(DEPT2 NOT NULL AND
DEPT 3 NOT NULL) OR
(DEPT1 NOT NULL AND
DEPT3 NOT NULL)

Hope that is helpful.

dick scherrer · Posted: Fri May 18, 2007 7:40 pm

Hello,

So long as DEPTn is defined to support NULL. . . .

SHAILESH OZA · New User Joined: 10 Jun 2005 Posts: 21 Location: Mumbai

one more thing you can do is . since you are having three department says COMP ,EXTC AND MECH. SO you can apply the following query also.

SELECT EMPNAME FROM EMP
where (DEPT1 = 'COMP' and DEPT2 = 'EXTC')
or (DEPT2 = 'EXTC' and DEPT3 = 'MECH')
or (DEPT3 = 'MECH' and DEPT1 = 'COMP')
or( DEPT1 = 'COMP' and DEPT2 = 'EXTC' and DEPT3 = 'MECH')

dick scherrer · Posted: Tue Jun 26, 2007 7:58 pm

Hello,

You will not want to hard-code specific values. That is (IMHO) a bad practice. Think of the nightmare if there were 200 departments at the company. . .

The only requirement was to identify those with more than 1 dept worked. . .

TG Murphy · Posted: Tue Jun 26, 2007 9:48 pm

Your EMPLOYEE table is not normalized.

There should only be 1 instance of DEPT on a single row.

If you are responsible for the table then consider normalizing it. If not responsible, ask the DBA why it is not normalized.

Bitneuker · Posted: Wed Jun 27, 2007 2:39 am