topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Wed, 05 Sep 2007 05:23:27 GMT

Hi All,

I want to know about "Inner Join" query...between 3 tables

Plz. give a suitable example of it.....and if possible snapshot also...

Thanks & Regards,

Anil Kumar

Re: Inner Join

Former Member — Wed, 05 Sep 2007 05:24:51 GMT

Inner joins using 3 tables

Try this :-

SELECT stpo_{stlnr stpo}idnrk mast_{matnr mara}mtart stpo~menge

INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

JOIN stpo ON stpo_{stlnr = mast}stlnr

JOIN mara ON mara_{matnr = mast}matnr

WHERE stpo_{stlty = 'M' "AND stpo}idnrk IN s_matnr

AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.

Code:

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

This code locates sales, delivery and payment terms info from a billing document number.

Here, this one also works fine :

select zfpcd_{cadivi zfpcd}proforma zfpcd_{factura zfpcd}aniofactura

zfpcd_{montousd zfpcd}montoap zfpcd_{ebeln zfpcd}inco1

zfpcd_{lifnr lfa1}name1 zcdvs_{status zfpcd}conint

into it_lista

from zfpcd inner join zcdvs

on zfpcd_{ebeln = zcdvs}ebeln

and zfpcd_{proforma = zcdvs}proforma

and zfpcd_{lifnr = zcdvs}lifnr

inner join lfa1

on zfpcd_{lifnr = lfa1}lifnr

where zcdvs~status = '04'.

reward if helpful

vivekanand

Re: Inner Join

Former Member — Wed, 05 Sep 2007 05:26:03 GMT

see ex

types : begin of t_fin ,

MATNR LIKE MARA-MATNR,

LVORM LIKE MARA-LVORM,

MTART LIKE MARA-MTART,

ERNAM LIKE MARA-ERNAM,

ERSDA LIKE MARA-ERSDA,

LAEDA LIKE MARA-LAEDA,

ZZITEMCODE LIKE MARA-ZZITEMCODE,

ZZSELNRRF LIKE MARA-ZZSELNRRF,

ZZITEMTYPE LIKE MARA-ZZITEMTYPE,

MAKTX LIKE MAKT-MAKTX,

spras like makt-spras,

VKORG LIKE MVKE-VKORG,

end of t_fin.

DATA : it_fin TYPE STANDARD TABLE OF T_fin,

wa_fin TYPE T_fin.

select a~matnr

a~lvorm

a~mtart

a~ernam

a~ersda

a~laeda

a~zzitemcode

a~zzselnrrf

a~zzitemtype

b~matnr

b~maktx

b~spras

c~matnr

c~vkorg

into corresponding fields of table it_fin

from mara as a join makt as b

on a_{matnr = b}matnr

join mvke as c on a_{matnr = c}matnr

where a~mtart in ('ROH' , 'Z009' ,'ZVSM') and

a_{zzitemtype = 'C' and b}spras = 'E'.

here join means by default innerjoin

Regards

sandhya

Re: Inner Join

Former Member — Wed, 05 Sep 2007 05:29:58 GMT

code similar to the example below :

SELECT J_1IEXCDTL_{ADDLDATA1 J_1IEXCDTL}ADDLDATA2 J_1IEXCDTL~CHARG

J_1IEXCDTL_{DOCNO J_1IEXCDTL}DOCYR J_1IEXCDTL~EXBAS

J_1IEXCDTL_{EXBED J_1IEXCDTL}EXCUR J_1IEXCDTL~LGORT

J_1IEXCDTL_{MAKTX J_1IEXCDTL}MATNR J_1IEXCDTL~MEINS

J_1IEXCDTL_{MENGR J_1IEXCDTL}RDOC2 J_1IEXCDTL_{WERKS MARD}LABST

MARD_{LFMON MARD}LGORT MARD_{MATNR MARD}WERKS MCHA~CHARG

MCHA_{LICHA MCHA}MATNR MCHA_{VFDAT MCHA}WERKS MARA~MATNR

MARA~MEINS

INTO (J_1IEXCDTL-ADDLDATA1 , J_1IEXCDTL-ADDLDATA2 , J_1IEXCDTL-CHARG

, J_1IEXCDTL-DOCNO , J_1IEXCDTL-DOCYR , J_1IEXCDTL-EXBAS

, J_1IEXCDTL-EXBED , J_1IEXCDTL-EXCUR , J_1IEXCDTL-LGORT

, J_1IEXCDTL-MAKTX , J_1IEXCDTL-MATNR , J_1IEXCDTL-MEINS

, J_1IEXCDTL-MENGR , J_1IEXCDTL-RDOC2 , J_1IEXCDTL-WERKS

, MARD-LABST , MARD-LFMON , MARD-LGORT , MARD-MATNR , MARD-WERKS

, MCHA-CHARG , MCHA-LICHA , MCHA-MATNR , MCHA-VFDAT , MCHA-WERKS

, MARA-MATNR , MARA-MEINS )

FROM

( J_1IEXCDTL

INNER JOIN MARD

ON MARD_{MATNR = J_1IEXCDTL}MATNR

AND MARD_{WERKS = J_1IEXCDTL}WERKS

AND MARD_{LGORT = J_1IEXCDTL}LGORT

INNER JOIN MCHA

ON MCHA_{MATNR = J_1IEXCDTL}MATNR

AND MCHA_{WERKS = J_1IEXCDTL}WERKS

AND MCHA_{CHARG = J_1IEXCDTL}CHARG

INNER JOIN MARA

ON MARA_{MATNR = MARD}MATNR )

WHERE J_1IEXCDTL~MENGR IN MENGR

AND J_1IEXCDTL~ADDLDATA1 IN SP$00006

AND J_1IEXCDTL~ADDLDATA2 IN SP$00007

AND J_1IEXCDTL~CHARG IN SP$00008

AND J_1IEXCDTL~LGORT IN SP$00002

AND J_1IEXCDTL~MAKTX IN SP$00003

AND J_1IEXCDTL~MATNR IN SP$00004

AND J_1IEXCDTL~MENGR IN SP$00012

AND J_1IEXCDTL~WERKS IN SP$00001

AND MARD~LFMON IN SP$00005

AND MCHA~LICHA IN SP$00009

AND MCHA~VFDAT IN SP$00010.

Regards,

Aparna