Infoset join behavior

[Former Member]

Dear Experts,

If Transaction data A is left outer joined (A is the the Left part) to transaction data B by 2 fields, and

this join from A finds 2 records in B based on the 2-field joins,

will the infoset list out 2 records like this :

A B B1 B2 B3 $10

A B B1 B2 B4 $15

where A = field in record in Transaction data A and

B, B1, B2, B3, B4 = fields in Transaction data B

and B1 to B4 cannot be joined to A as A does not have similar fields to join with.

?

Please advise.

Thanks!

regards

Bass

[Former Member · Accepted solution]

Hi,

Your question is not clear to me but i will explain Left outer join

For eg: you have 2 tables A and B

A B

1 5

2 6

3 3

4 4

when you do left outer join ,all values from A will be selected+ Common values in B ( total 6 values will be selected). That means result will be like this

1 -

2 -

3 3

4 4

I hope this will help you

krish

Edited by: kris202729 on Nov 20, 2011 9:11 PM

[Former Member · Accepted solution]

Hi Krish

My situation is like this :

A B

1 5

2 6

3 3

4 3

For 1 record in A, there are 2 matching ones in B.

So, will the infoset display :

A B

3 3

3 3

?

This means, will both matching records in B be displayed or will only 1 of the 2 found in B be displayed?

regards

Bass

[Former Member · Accepted solution]

Hi Bass,

According to your requirement

A B

1 5

2 6

3 3

4 3

if you are using left outer join then result would be:

1 null value

2 null value

3 3

4 null value

Left outer join means all the values from A table+ values which are common in B are selected.

In inner join only common values are selected so result would be:

3 3

krish

[Former Member · Accepted solution]

Hi Bass,

you are performing join on a non - key field.

the records will be displayed.

T1,

A X

B Y

C Z

T2,

P X H

Q X I

R X J

If you perform T1 left outer join T2 on field2(of T1) and field2(of T2).

output will be

(obviously you are3 interested in f1 of T1 and f3 of T2)

A H

A I

A J

B -

C -

hope the explanation helps < output analyzed with the help of abap program and abap left outer join statement>

cheers

[Former Member · Accepted solution]

@Bass, Krish

I slightly disagree with you Krish. The result of the left outer join:

1 null value

2 null value

3 3

3 3

4 null value

Donu2019t be fooled by the value of the key. Every value matched in the right hand side will be included in the result set. Check this link.

http://www.w3schools.com/sql/sql_join_left.asp

Hansen, Pettersen 2 rows each.

Cheers.

Edited by: Passing By on Nov 21, 2011 7:04 AM

[Former Member · Accepted solution]

Dear Passing By

I agree with you!

It seems so in my unit testing.

I will confirm this again and then close this thread unless there are some more input from the other Gurus.

Have a great day!

Regards

Bass