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
 

 

DB2 Select Query

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

New User


Joined: 27 Jul 2007
Posts: 33
Location: mumbai

PostPosted: Tue Oct 30, 2007 2:11 pm    Post subject: DB2 Select Query
Reply with quote

Hi,

1) Table A

A1 A2
25 11
25 11
50 22
75 33
100 44
125 55
150 66
175 77
175 77

2) Table B

A2 B2 B3
11 90V Y
11 90V N
22 90V Y
33 90V N
44 30T Y
55 90V Y
66 90V N
77 90V N
77 90V Y

Result Table

A1
75
150

I have two tables Table A and Table B. Please tell me how the query will look like if I want the result table as shown above.
The condition to retrieve the result table is:
Relation of two table indicates column A2.
B2 = 90V and
B3= N

Thank You.
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Oct 30, 2007 2:17 pm    Post subject:
Reply with quote

Vaibhav,

What happend to other A1 values like 25, 177? You should be clear with your requirement. We cant read what is there in your mind.
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Oct 30, 2007 4:48 pm    Post subject: Reply to: DB2 Select Query
Reply with quote

According to the condition you have mentioned i.e, B1=90v and b2=n you will get get output
:-
25
75
150
175

not:-75
150
Back to top
View user's profile Send private message
vaibhavjadhav

New User


Joined: 27 Jul 2007
Posts: 33
Location: mumbai

PostPosted: Wed Oct 31, 2007 4:04 pm    Post subject: Reply to: DB2 Select Query
Reply with quote

the condition is only B2 = N for the related value of A1.
if A1 has two same values like 175 and the related values in B2 are Y and N then the query would not retrieve A1's 175 value. it should retrieve only two values 75 and 150 as shown in the result table.
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: Chennai

PostPosted: Wed Oct 31, 2007 4:18 pm    Post subject:
Reply with quote

HiVaibhav,

This is the query for your requirement.

Code:
SELECT A1 FROM TABLE_A

WHERE

TABLE_A.A2 = (SELECT A2 FROM TABLE_B

WHERE

TABLE_B.B2 = '90V'      AND
TABLE_B.B3 = 'N' );



If you have any doubt regard this let us know.
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Wed Oct 31, 2007 4:30 pm    Post subject: Reply to: DB2 Select Query
Reply with quote

HI,
this is your 2 tables
1) Table A

A1 A2
25 11
25 11
50 22
75 33
100 44
125 55
150 66
175 77
175 77

2) Table B

A2 B2 B3
11 90V Y
11 90V N
22 90V Y
33 90V N
44 30T Y
55 90V Y
66 90V N
77 90V N
77 90V Y

and your condition is B2=90v and B3=N

In Table B 4 records satisfying this condition i.e, B2=90V and B3=N

A2 B2 B3
11 90V N
33 90V N
66 90V N
77 90V N

So according to Your condition resultant table will be

A1
25
75
150
175


If this is the required output let us know we will post Query for this

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 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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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