DB2 Select Query

vaibhavjadhav · New User Joined: 27 Jul 2007 Posts: 33 Location: mumbai

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.

murmohk1 · Posted: Tue Oct 30, 2007 2:17 pm

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.

roopannamdhari · New User Joined: 14 Sep 2006 Posts: 71 Location: Bangalore

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

vaibhavjadhav · New User Joined: 27 Jul 2007 Posts: 33 Location: mumbai

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.

hemanth.nandas · Active User Joined: 18 Aug 2007 Posts: 120 Location: India

HiVaibhav,

This is the query for your requirement.

roopannamdhari · New User Joined: 14 Sep 2006 Posts: 71 Location: Bangalore

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,