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
 

 

SQL Qstn

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

New User


Joined: 03 Apr 2008
Posts: 82
Location: United States

PostPosted: Mon Dec 07, 2009 2:52 pm    Post subject: SQL Qstn
Reply with quote

I have a DB2 table:

Columns:

Emp ID
Emp Name
Emp Age
Emp Manager
Manager's ID

I ned all Emp ID and Name whose Age is greater than their respective managers age.

Pls suggest the best solution.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 07, 2009 3:13 pm    Post subject:
Reply with quote

What have you tried so far?

Do a self join of the table on emp_id and manager_id with the age condition or else use a correlated subquery for the age .
Back to top
View user's profile Send private message
CICS fan

New User


Joined: 03 Apr 2008
Posts: 82
Location: United States

PostPosted: Mon Dec 07, 2009 3:16 pm    Post subject:
Reply with quote

I am in sea with this. Could u be a bit specific kindly.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 07, 2009 3:20 pm    Post subject:
Reply with quote

Quote:

I am in sea with this


??

Code:

SELECT EMP_ID FROM TABLE A
WHERE EMP_AGE > (SELECT MANAGER_AGE FROM TABLE B
WHERE EMP_ID = A.EMP_ID)
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Dec 07, 2009 4:28 pm    Post subject:
Reply with quote

Hello There,

I think below query will give desired results

Code:
Select EMP_ID from Table A
Where Emp_Age >( Select Emp_Age From Table B
                             Where A.Manager's_ID =B.Emp_id)
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:



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