Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need a query to display employees worked at multiple branch

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
komal

New User


Joined: 09 May 2005
Posts: 18

PostPosted: Tue May 03, 2011 5:15 pm    Post subject: Need a query to display employees worked at multiple branch
Reply with quote

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
View user's profile Send private message

prino

Active Member


Joined: 07 Feb 2009
Posts: 989
Location: Oostende, Belgium

PostPosted: Tue May 03, 2011 5:23 pm    Post subject: Re: Need a query to display employees worked at multiple bra
Reply with quote

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
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1458
Location: Azeroth

PostPosted: Tue May 03, 2011 5:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Tue May 03, 2011 5:43 pm    Post subject: Reply to: Need a query to display employees worked at multip
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 03, 2011 9:17 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed May 04, 2011 4:50 pm    Post subject:
Reply with quote

GuyC wrote:
Instead of feeling superior it would be better to just help the guy.
Like! icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts Display ZEDLMSG without Pressing F1 &... anandgbe CLIST & REXX 8 Thu Dec 15, 2016 4:20 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us