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
 

 

Query to find multiple rows

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

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Mon Jan 13, 2014 12:48 pm    Post subject: Query to find multiple rows
Reply with quote

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

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Jan 13, 2014 7:37 pm    Post subject: Reply to: Query to find multiple rows
Reply with quote

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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jan 15, 2014 8:24 am    Post subject:
Reply with quote

Chandan , shouldn't that count be just = 1 instead of > 1 ?
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Wed Jan 15, 2014 9:09 am    Post subject:
Reply with quote

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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jan 15, 2014 9:11 am    Post subject:
Reply with quote

Thanks suresh...I took the requirement in the reverse way.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jan 15, 2014 11:50 am    Post subject: Reply to: Query to find multiple rows
Reply with quote

Agree with you Suresh..Thanks
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 How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


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