Issue with inner join

Former Member — Fri, 11 Jan 2008 17:02:53 GMT

I have 2 tables z1 and z2

z1 has f1 ,f2 and f3 fields with f1 and f2 as primary keys

and z2 has f4, f2,f3 and f5 fields with f4 and f2 as primary keys

I have to get f5 value. I have f1 and f2 values as my input

I am retreiving f3 value from z1 based on f1 and f2 and i am using retreived value of f3 from z1 and f2 on z2 to get f5 value

ex :

1 a b 9

2 a b 9

3 a b 8

4 a b 9

DATA : test LIKE z2.

clear test.

sELECT single b_{f4 b}f2 b_{f3 b}f5

INTO test

FROM z2 AS b INNER JOIN z1 AS a

ON b_{zf3 = a}zf3 AND

b_{zf2 = a}zf2

WHERE a~f1 = p_f1

AND a~f2 = p_f2.

Iam getting 3 record i.e 3 a b 8

and if increase the records in z2 its gives me someother record its picking a random record . I thought that it would bring the first record

Let me know

Thanks

Re: Issue with inner join

Former Member — Fri, 11 Jan 2008 18:43:58 GMT

Hi ,

ANY SUUGESTIONS

Thanks

Former Member — Fri, 11 Jan 2008 20:45:51 GMT

Hi Venki,

Can you please give us more details like how your z1 and z2 tables look like and what is the result you expect out of your inner join?

Thanks

Sanjeev