topic Re: Oute Join and Inner Join in Application Development and Automation Discussions

Oute Join and Inner Join

Former Member — Fri, 02 Jun 2006 06:59:13 GMT

Hi,

I need to join three tables, based on some conditions (EKPO, EKBE and EKKN Note: All PO line items from EKPO will have a movement associated in the EKBE Table. Capture all PO line items where no matches found in the EKPO-EKBE join)

for the above requirement shall I write my query like this?

SELECT ekpo~ebeln

ekpo~ebelp

ekpo~loekz

ekpo~txz01

ekpo~matnr

ekpo~bukrs

ekpo~werks

ekpo~menge

ekpo~meins

ekpo~knttp

ekbe~vgabe

ekbe~bwart

ekbe~menge

ekbe~dmbtr

ekbe~shkzg

ekkn~sakto

ekkn~kostl

ekkn~ps_psp_pnr

INTO CORRESPONDING FIELDS OF TABLE i_podata

FROM ekpo LEFT OUTER JOIN ekbe ON ekbe_{ebeln = ekpo}ebeln AND

ekbe_{ebelp = ekpo}ebelp

INNER JOIN ekkn ON ekkn_{ebeln = ekpo}ebeln AND

ekkn_{ebelp = ekpo}ebelp

WHERE ekpo~werks IN s_werks AND

ekpo~ebeln IN s_ebeln.

Shall I use both Outer Join and Inner join in one Query?

Please correcte me.

Thanks

Frank Rex

Re: Oute Join and Inner Join

Former Member — Fri, 02 Jun 2006 07:17:09 GMT

Hi,

You can use both inner join and outer join in the same select statement.

Ensure first all the inner joins between tables are declared and put the left outer join at the end.

Some sample code for your reference:

SELECT

A_{VBELN A}KUNNR A_{BSTNK A}BSTDK A_{VKORG A}VTWEG A_{AUART A}KNUMV

B_{POSNR B}MATNR B_{WERKS B}SPART B_{LGORT B}KZWI1

SUM( B_{KWMENG ) AS KWMENG D}BZIRK D~VKGRP

E_{DISPO E}PRCTR

FROM VBAK AS A INNER JOIN VBAP AS B ON A_{VBELN EQ B}VBELN

INNER JOIN VBPA AS C ON C_{VBELN EQ B}VBELN

INNER JOIN KNVV AS D ON D_{KUNNR EQ A}KUNNR

AND D_{VKORG EQ A}VKORG AND D_{VTWEG EQ A}VTWEG

LEFT OUTER JOIN MARC AS E ON E~MATNR EQ

B_{MATNR AND E}WERKS EQ B~WERKS

INTO CORRESPONDING FIELDS OF TABLE IT_ORDERS

WHERE A~VKORG IN SO_VKORG

AND A~VTWEG IN SO_VTWEG

AND A~KUNNR IN SO_KUNNR

AND A~ERDAT IN SO_ERDAT

AND A~AUART IN ('ZFOR','ZROR','ZEOR','ZDXR','ZXOR','ZRM1','ZGOR','ZSOR')

AND B~MATNR IN SO_MATNR

AND B~WERKS IN SO_WERKS

AND B~SPART IN SO_SPART

AND B~ABGRU EQ SPACE

AND A~LIFSK EQ SPACE

AND A~FAKSK EQ SPACE

AND B~VSTEL IN SO_VSTEL

AND B~LGORT IN SO_LGORT

AND C~KUNNR IN SO_SHIP

AND C~PARVW EQ 'WE'

AND D~VKGRP IN SO_VKGRP

AND D~BZIRK IN SO_BZIRK

AND B~LGORT NE '0950'

GROUP BY A_{VBELN A}KUNNR A_{BSTNK A}BSTDK

A_{VKORG A}VTWEG A_{AUART A}KNUMV B~POSNR

B_{MATNR B}WERKS B_{SPART B}KZWI1 D~BZIRK

D_{VKGRP B}LGORT E_{DISPO E}PRCTR E~MATGR.

Lakshminarayanan.

P.S.Mark all helpful answers for points.

Re: Oute Join and Inner Join

Former Member — Fri, 02 Jun 2006 07:17:22 GMT

Hi,

I think it's better to use 4 Internal Table, like this:

1. Select from EKPO into Internal IT_EKPO
2. Select from EKBE into Internal IT_EKBE
   with: FOR ALL ENTRIES IN IT_EKPO
         WHERE ebeln = TIT_EKPO-ebeln.
3. Select from EKKN into Internal IT_EKKN
   with: FOR ALL ENTRIES IN IT_EKPO
         WHERE ebeln = TIT_EKPO-ebeln
               EBELP = TIT_EKPO-EBELP.
4. Join table IT_EKPO, IT_EKBE and IT_EKKN into one Internal Table as the Result Table by Looping process.

I hope this , help u.

Regards,

Re: Oute Join and Inner Join

Former Member — Fri, 02 Jun 2006 07:25:07 GMT

Hi Frank,

Use <b>Inner Join</b> for the same

Regards,

Santosh

Re: Oute Join and Inner Join

dani_mn — Fri, 02 Jun 2006 07:35:53 GMT

Outer join will do the job for you.

Because inner join skips the record where no matches found.

Regards,

Wasim Ahmed

Re: Oute Join and Inner Join

Former Member — Fri, 02 Jun 2006 08:40:26 GMT

Using Inner Join You may miss out some fields as you should be careful to choose all the key fields. Instead you do also READ TABLE with BINARY SEARCH