In a single table,How to retrieve a employee-id of an
employee who works in more than one department?
Answers were Sorted based on User's Feedback
Answer / varun
Select emp_id
from
employee
group by emp_id,dept
having count(*)>1;
Is This Answer Correct ? | 26 Yes | 2 No |
Answer / krishnakumar
select employee-id
from table-name
grtoup by employee-id
having count(*) > 1
Is This Answer Correct ? | 13 Yes | 1 No |
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 |
Answer / venkat
select emp-id from emp group by emp-id having count(*)>1;
Is This Answer Correct ? | 3 Yes | 0 No |
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 |
Answer / krishnakumar
Ans 5 is also correct but it seems a bit compliceted
Is This Answer Correct ? | 2 Yes | 0 No |
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 |
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 |
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 |
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 |
Where could you look if you had a question about whether a column has been defined as an index?
What is meant by index cardinality?
DB2: A calls B once sucessfull and second time when it calls says curson is open why is it so?
What does the sqlcode of -818 pertain to?
Can SQL statements/queries be included in a copybook?
What is subquery ?
Parm value passed is PARM=(10,20). how do code linkage section and how would you add these two passes values and show result in cobol program.
Name some fields from SQLCA.
What is the syntax for FETCH in DB2 ?
suppose in my table 10 rows are there , i want to update odd rows salary as 90000 ? how u do it ? any one help me ? what do we use here cursor-fetch or normal sql ?
I need to view the number of tables existing under one particular Owner. Is it possible? If so, pl give the SQL query for this?
How to find the number of rows in db2 tables?