topic join condition in Application Development and Automation Discussions

join condition

Former Member — Thu, 17 Nov 2005 21:17:26 GMT

Hi,

i have a requirement to code a join condition.

~~table A~~

field1

field2

field3

fielda

fieldb

~~table B~~

field1

field2

fieldc

i need to retrieve values from these tables in such a way that

table A-field1 = table B-field1 and

table A-field2 = table B-field2 and

table A-fielda = '100'

table A-fieldb = '200'

table B-fieldc IN sel_option.

TABLE A contains less entries compared to TABLE B. hence i coded the join condition like this

SELECT table_A_{field1 table_A}field2 table_A~field3

table_B~fieldc INTO TABLE t_jointable

FROM table_B

INNER JOIN table_A

ON table_A_{field1 = table_B}field1

AND table_A_{field2 = table_B}field2

WHERE

table_A~fielda = '100'

AND table_A~fieldb = '200'

AND table_B~fieldc IN s_selop.

it doesn't work. did i miss something. your help would be appreciated.

Thanks,

kranthi.

Re: join condition

Former Member — Thu, 17 Nov 2005 21:22:54 GMT

This can be because you are using INNER join. Check if entries are present in Table A also. If the entries are not present in table A but only in table B, this will not pick up any details in output internal table.

Re: join condition

Former Member — Thu, 17 Nov 2005 21:23:23 GMT

I think COde is ALrite.

But What is ur problem exactly.

are u getting some thing or not.

Check the Data in table once .

regards

vijay

Re: join condition

RichHeilman — Thu, 17 Nov 2005 21:26:24 GMT

It look pretty good to me.... I would swap the FROM INNER JOIN. Are you sure that fielda and fieldb are stored like "100" or "200" in the database. Could they be being stored as "0100" or something like that?



SELECT table_A~field1 table_A~field2
       table_A~field3 table_B~fieldc 
         INTO TABLE t_jointable
<b>         FROM table_A
                 INNER JOIN table_B</b>
                   ON table_A~field1 = table_B~field1
                  AND table_A~field2 = table_B~field2
                     WHERE table_A~fielda = '100'
                       AND table_A~fieldb = '200' 
                       AND table_B~fieldc IN s_selop.

Regards,

Rich Heilman

Re: join condition

Former Member — Thu, 17 Nov 2005 21:28:32 GMT

There is nothing wrong with the code.

I think check once your Data in database.

Regards

vijay