|
View previous topic :: View next topic
|
| Author |
Message |
komal
New User
Joined: 09 May 2005
Posts: 17
|
|
|
|
I am having a table with hundreds of rows shown below
EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
2345 ABC123
3456 ABC123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123
I need a query to display only the employee rows who worked in multiple
branches. See below
EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123
I dont want to disply the rows
2345 ABC123
3456 ABC123
Can some one please give me the query. |
|
| Back to top |
|
 |
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
| komal wrote: |
I am having a table with hundreds of rows shown below
EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
2345 ABC123
3456 ABC123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123
I need a query to display only the employee rows who worked in multiple
branches. See below
EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123
I dont want to disply the rows
2345 ABC123
3456 ABC123
Can some one please give me the query. |
Just transfer EUR 700 to my PayPal account, and you'll get your query.
THIS IS A FLUCKINGH HELP FORUM, NOT A DO MY WORK FOR ME ONE! |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Logic:
Use a count operator on EMPNO,
use a group by on empno having count > 1
Use order by EMPNO,BRANCH.
Writing the query is upto you.
Hope it helps.
(Tested on SAS PROC SQL) |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10872
Location: italy
|
|
|
|
| Your first post dealing with db2/sql was in 2006 after five years You should have learned how to search and how to ask questions |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I admit HAVING is a basic SQL-element that everyone should know, but one good example is all the poor TS needs.
Instead of feeling superior it would be better to just help the guy.
| Code: |
| SELECT COL1 from tab1 GROUP BY COL1 HAVING COUNT(*) > 1 |
would give you all the COL1 with multiple rows in the table.
It won't give you all the rows, just all the distinct COL1 values .
If you actually need all the corresponding rows, you would need to join this result with the original table. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| GuyC wrote: |
| Instead of feeling superior it would be better to just help the guy. |
Like!  |
|
| Back to top |
|
|
|
|
