regarding join statement

[Former Member]

SELECT vbrk_{vbeln vbfa}vbelv

vbrk_{fkdat vbrk}kunag

vbrp_{werks kna1}name1

FROM vbrk

INNER JOIN vbrp ON vbrk_{vbeln = vbrp}vbeln

INNER JOIN vbfa ON vbfa_{vbeln = vbrk}vbeln

INNER JOIN kna1 ON kna1_{kunnr = vbrk}kunag

INTO CORRESPONDING FIELDS OF TABLE INVTAB

WHERE vbrp~werks IN s_werks

AND vbrk~fkdat IN s_fkdat

AND vbrk~kunag IN s_kunag

AND vbrk~fkart IN ('ZF2','FVAT')

AND vbrk~fksto <> 'X'

AND vbrk~rfbsk = 'C'

AND vbrk~sfakn = ''

AND vbrk~inco1 = 'TWO'

AND VBRK~VKORG NE '5000'

AND vbfa~vbtyp_n = 'M'

AND vbfa~vbtyp_v = 'J'.

i am writng this join statement

but when displaying i am getting multiple records for a particular entry

say for a particular documnet number if it is having N no. of line itmes and when searching in vbfa thenumber of preeecig documents are M then the resords that it is disp;laying is N*M.

but in some cases it is showing records more than this

can you please help me why it is showing more records.

and in if you can explain me with an example with a dummy no of records how it will fetch record that will be a great help.

[former_member198270]

Hi Ashish,

please chk the following links for inner and outer join ,

http://help.sap.com/saphelp_erp2004/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

http://www.sap-img.com/abap/several-nested-inner-join-statements-can-be-inefficient.htm

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp

now how to write join statements

SELECT

a~ebeln

a~ebelp

a~matnr

a~txz01

a~menge

a~meins

a~netpr

a~brtwr

a~netwr

a~mwskz

a~navnw

a~effwr

a~werks

b~eindt

FROM

ekpo AS a

INNER JOIN

eket AS b

ON

a_{ebeln = b}ebeln

INTO

TABLE t_ekpo

WHERE

a~ebeln = t_ekko_temp-ebeln

AND b~ebeln = t_ekko_temp-ebeln.

Think there are 2 tables . a right hand table and a left hand table.

in inner join : only those fields having coresponding values in the left hand side table are selected.

in outer join all the fields on the left hand side table are selected and the fields which have no corresponding values in the right hand side table are displayed as null values..

Reward points if helpful

Regards ,

Amber S

[Former Member]

Hi

Why you are using VBFA table?

To fetch the related delivery doc for the invoice doc?

take it from VBRP table VGBEL field

don't take VBFA table as join?

SELECT vbrk_{vbeln vbfa}vbelv

vbrk_{fkdat vbrk}kunag

vbrp_{werks vbrp}vgbel kna1~name1

FROM vbrk

INNER JOIN vbrp ON vbrk_{vbeln = vbrp}vbeln

INNER JOIN kna1 ON kna1_{kunnr = vbrk}kunag

INTO CORRESPONDING FIELDS OF TABLE INVTAB

WHERE vbrk~fkdat IN s_fkdat

AND vbrk~kunag IN s_kunag

AND vbrk~fkart IN ('ZF2','FVAT')

AND vbrk~fksto <> 'X'

AND vbrk~rfbsk = 'C'

AND vbrk~sfakn = ''

AND vbrk~inco1 = 'TWO'

AND VBRK~VKORG NE '5000'

AND vbrp~werks IN s_werks.

check now and see

Reward points for useful Answers

Regards

Anji

[Former Member]

why we should not use vbfa in join statement any particular reason?

and will this vbrp field vgbel will solve my purpose of getting no of preeceeding documents?

[Former Member]

Hi,

SELECT VBRK_{VBELN VBFA}VBELV VBRK_{FKDAT VBRK}KUNAG

VBRP_{WERKS KNA!}NAME1

FROM ( VBRK

INNER JOIN VBRP

ON VBRP_{VBELN = VBRK}VBELN

INNER JOIN VBFA

ON VBFA_{POSNV = VBRP}POSNR

AND VBFA_{POSNN = VBRP}UEPOS

AND VBFA_{VBELV = VBRP}VBELN

AND VBFA_{VBELN = VBRP}VBELV

AND VBFA_{VBTYP_N = VBRP}VGTYP

INNER JOIN KNA1

ON KNA1_{EKONT = VBFA}VBELV )

INTO CORRESPONDING FIELDS OF TABLE INVTAB

WHERE vbrp~werks IN s_werks

AND vbrk~fkdat IN s_fkdat

AND vbrk~kunag IN s_kunag

AND vbrk~fkart IN ('ZF2','FVAT')

AND vbrk~fksto <> 'X'

AND vbrk~rfbsk = 'C'

AND vbrk~sfakn = ''

AND vbrk~inco1 = 'TWO'

AND VBRK~VKORG NE '5000'

AND vbfa~vbtyp_n = 'M'

AND vbfa~vbtyp_v = 'J'.

Try with this code.

Don't forget to reward if useful

[Former Member]

Hi Ashish,

Firstly avoid using join statement.

It may seem simple while you are joining two tables but when it comes to joining multiple tables it becomes an uphill task.

Use the following alternaties.

They are simple.

To select single entries use this.

Select single field1 field2 fieldn

from table

into x_tab

where tab_field = table_field.

To select multiple entries use this.

Select field1 field2 fieldn

from table

into it_tab

for all entries in table

where tab_field = table_field.

This should solve all your joining problems with select queries.

Reward Points if useful.

Thanks,

Tej..