View previous topic :: View next topic
|
Author |
Message |
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
Back to top |
|
|
ashwinreddy
Active User
Joined: 16 Sep 2004 Posts: 106 Location: Hyderabad
|
|
|
|
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 |
|
Back to top |
|
|
mahi
New User
Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad
|
|
Back to top |
|
|
vicky10001 Warnings : 1 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. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
The previous dialog has me confused
If i read this
Quote: |
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. |
correctly, Arun wants a return of those who have more than 1 dept "active" (i.e. works in more than 1 dept - the code has a "real" value).
Ashwin's suggestion (i've re-structured it to make it take less space)
Code: |
Select EMPNO,EMPNAME
From EMPL
where (DEPT1 > 0 and DEPT2 > 0)
or (DEPT2 > 0 and DEPT3 > 0)
or (DEPT3 > 0 and DEPT1 > 0) |
looks ok as long as dept is numeric - if it is alpha, the test could be for not= spaces rather than >zero.
Please let me know if i've misunderstood. . . |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Hi all,
Thanks for the response
Arun |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
Raphael Bacay
New User
Joined: 04 May 2007 Posts: 58 Location: Manila, Philippines
|
|
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
So long as DEPTn is defined to support NULL. . . . |
|
Back to top |
|
|
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') |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. . . |
|
Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
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. |
|
Back to top |
|
|
Bitneuker
CICS Moderator
Joined: 07 Nov 2005 Posts: 1104 Location: The Netherlands at Hole 19
|
|
|
|
TG Murphy wrote: |
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. |
Fully agreed
We are looking an n:m relationship. Entity employee is one and department is the other. In this relationship between entities an employee can work in 1:n departments and, in a departement 1:n employees can work. This causes the n:m relationship. The solution is a relation-entity between both. We call this data modelling (TG understands this) including normalisation towards the 5th form. You will probably end up with an extra table containing the keys for employees and departments. |
|
Back to top |
|
|
|