Skip to Content
0
Nov 08, 2012 at 03:31 PM

Left outer join

41 Views

Let say Table A has 2 columns and has the following value

Col1 Col2

ABC 123

DEF 345

GHI 678

Then Table B has 2 columns and has the following value

Col1 Col2

345 Main St.

768 Wall St.

as far as I understood left outer join will give me:

select a.col1, a.col2, b.col2 ad col3 from A as A left outer join B as B on a.Col2 = b.Col1;

Col1 Col2 Col3

ABC 123 ?

DEF 345 Main St.

GHI 678 Wall St.

However, I only get

Col1 Col2 Col3

DEF 345 Main St.

GHI 678 Wall St.

Which looks like a inner join. Please see the screen shot below for the actual data i'm using:

select

* from "_SYS_BIC"."com.isantoso.poc/SOURCE_DATA" where kunnr = '0000120207' order by matnr;

gives me:

Please note that on the second table, only material number starting with WDE exist.

select

a.mandt, a.kunnr, a.matnr, a.vkorg, a.vtweg, a.spart, a.pltyp, a.waers, a.werks,

knumh, kschl, kbetr, konwa, kpein, kmein

from "_SYS_BIC"."com.isantoso.poc/SOURCE_DATA" as A left outer join

"_SYS_BIC"."com.isantoso.poc/ZPRO_UNION_TEST2" as B

on a.mandt = b.mandt

and a.vkorg = b.vkorg

and a.vtweg = b.vtweg

and a.kunnr = b.kunnr

and a.matnr = b.matnr

where a.mandt = '130'

and b.kappl = 'V'

and a.vkorg = '1000'

and a.vtweg = '10'

and b.kschl = 'ZPR0'

and a.kunnr = '0000120207'

and b.pltyp is null

and b.waerk is null

and b.matnr is not null

and KFRST = ''

order by a.matnr;

gives me the following, which I thought all entry for material number not starting with WDE should also displayed with ? for the data from the second table

Attachments

pic1.png (20.8 kB)
pic2.png (38.4 kB)