|
View previous topic :: View next topic
|
| Author |
Message |
sasanka
Warnings : 1
New User
Joined: 18 Jan 2008
Posts: 34
Location: India
|
|
|
|
Hi,
We have an Employee Table (EMP_TAB) with columns EMP_NO, EMP_NAME, EMP_DEPT and data in the table are as below:
| Code: |
EMP_NO EMP_NAME EMP_DEPT
100 James Sales
200 Thomas Sales
300 Jacob Marketing
400 Mark Manufacturing
500 Eliza Advertising
600 Julia HR
700 Tom Manufacturing
800 Sandy Marketing |
We need a query to find only those employee details where more than one employee is available in a dept.
Out put we need is as below:
(since Advertising and HR dept has only one employee, so we don't need these respective rows in my query result)
| Code: |
EMP_NO EMP_NAME EMP_DEPT
100 James Sales
200 Thomas Sales
300 Jacob Marketing
400 Mark Manufacturing
700 Tom Manufacturing
800 Sandy Marketing |
Please help me with this query.
Thanks. |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
What you have tried so far?
See if below query helps
| Code: |
SELECT
A.EMP_NO,
A.EMP_NAME
FROM EMPLOYEE A
WHERE
A.EMP_DEPT
IN
(SELECT EMP_DEPT
FROM EMPLOYEE
GROUP BY EMP_DEPT
HAVING COUNT(EMP_DEPT ) > 1) |
Regards,
Chandan |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3052
Location: NYC,USA
|
|
|
|
| Chandan , shouldn't that count be just = 1 instead of > 1 ? |
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
Hi Rohit,
Whatever coded by Chandan is the correct one as per the requirement.
| Quote: |
| We need a query to find only those employee details where more than one employee is available in a dept. |
But a small modification to improve performance (Since we would have very less department which has only one employee, so it would take small amount of time for sub-query to execute).
| Code: |
SELECT *
FROM EMPLOYEE A
WHERE A.EMP_DEPT
NOT IN
(SELECT EMP_DEPT
FROM EMPLOYEE
GROUP BY EMP_DEPT
HAVING COUNT(EMP_DEPT) = 1)
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3052
Location: NYC,USA
|
|
|
|
| Thanks suresh...I took the requirement in the reverse way. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Agree with you Suresh..Thanks |
|
| Back to top |
|
|
|
|
