In a single table,How to retrieve a employee-id of an employee who works in mo

Categories >> Software >> MainFrame >> IBM MainFrame >> DB2
Suggest New Category

In a single table,How to retrieve a employee-id of an
employee who works in more than one department?

Question Posted / anagha

15 Answers
18765 Views
Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / varun

Select emp_id
from
employee
group by emp_id,dept
having count(*)>1;

Is This Answer Correct ?

26 Yes

2 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / krishnakumar

select employee-id
from table-name
grtoup by employee-id
having count(*) > 1

Is This Answer Correct ?

13 Yes

1 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / manikandan.d

hi all i hope this answer will help you

emp
eid ename dep
001 manikandan mainframe
002 karthi java
003 jarin mainframe
001 manikandan java
003 jarin testing
004 hariharan java
001 manikandan testing

select eid from emp e1 where 1 <

(select count(*) from emp e2 where e1.eid=e2.eid)

let me know if any new answer

Is This Answer Correct ?

7 Yes

2 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / venkat

select emp-id from emp group by emp-id having count(*)>1;

Is This Answer Correct ?

3 Yes

0 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / vish

Another classic example of people replying to the queries
in hurry..the answer #1, #2, #3, #4 & #7 are WRONG ANSWERS.
Answer #6 is correct. Answer #5 also seems to be correct to
me (unfortunatley I can't run & verify it at the moment,
someone please verify it & let everyone know).

Ram.g, FYI..EXISTS clause just denotes whether query after
EXISTS clause returned any row or not, it NEVER give a
count of the number of rows returned. Here need to find if
there are more than one rows (i.e count is needed) so
EXISTS can't be used here.

Is This Answer Correct ?

2 Yes

0 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / krishnakumar

Ans 5 is also correct but it seems a bit compliceted

Is This Answer Correct ?

2 Yes

0 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / sri

select A.empid from EMP A, EMP B where
A.empid = B.empid and
A.dept not = B.dept

Is This Answer Correct ?

2 Yes

0 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / ram.g

ans 5 need to be corrected...

subquery will fetch correct result but main query hw it
will compare... ???

it supposed to be where eid exists(select eid...where
e1.eid= e2.eid)

Is This Answer Correct ?

2 Yes

1 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / millar

select employee_id,department_id from emp where employee_id in(select employee_id from emp where department_id in (select distinct
department_id from emp) group by employee_id,department_id having count(*) >1);

Is This Answer Correct ?

0 Yes

1 No

In a single table,How to retrieve a employee-id of an employee who works in more than one departme..

Answer / anonymous

select emp_id from employee where dept in (select distinct
dept from employee) group by emp_id having count(*) >1;

Is This Answer Correct ?

0 Yes

2 No

Post New Answer