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: 1651
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: 1651
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 Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 6 Fri Mar 24, 2017 2:24 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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