Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us