topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Mon, 10 Sep 2007 05:17:03 GMT

Hi,

I am not getting how to inner join two tables(any two internal tables).

Vinay

Re: Inner join

Former Member — Mon, 10 Sep 2007 05:18:55 GMT

HI,

see this

TABLES:YTAB1,YTAB2.

DATA:BEGIN OF ITAB OCCURS 0,

ECODE LIKE YTAB1-ECODE,

ENAME LIKE YTAB1-ENAME,

DNO LIKE YTAB2-DNO,

SALARY LIKE YTAB1-SALARY,

CTYPE LIKE YTAB1-CTYPE,

DNAME LIKE YTAB2-DNAME,

END OF ITAB.

SELECT YTAB1_{ECODE YTAB1}ENAME YTAB1_{DNO YTAB1}SALARY YTAB1~CTYPE

YTAB2~DNAME INTO (ITAB-ECODE,ITAB-ENAME,ITAB-DNO,ITAB-SALARY,ITAB-CTYPE

,ITAB-DNAME) FROM YTAB1 INNER JOIN YTAB2 ON YTAB1_{DNO = YTAB2}DNO.

WRITE:/ ITAB-ECODE,ITAB-ENAME,ITAB-DNO,ITAB-SALARY,ITAB-CTYPE,

ITAB-DNAME.

ENDSELECT.

rgds,

bharat.

Re: Inner join

Former Member — Mon, 10 Sep 2007 05:19:29 GMT

Hi,

SELECT A~MATNR

A~ERSDA

A~ERNAM

A~LAEDA

A~AENAM

A~LVORM

A~MTART

A~MATKL

A~PRDHA

B~WERKS

C~MAKTX

FROM MARA AS A INNER JOIN

MARC AS B ON

A_{MATNR = B}MATNR

INNER JOIN MAKT AS C

ON A_{MATNR = C}MATNR INTO TABLE

GI_FINAL WHERE SPART = ' '

AND ekgrp = ' '

and dispo = ' '

and prctr = ' '

and spras = 'EN'.

regards

siva

Re: Inner join

Former Member — Mon, 10 Sep 2007 05:21:48 GMT

Hi,

Iner join is a term used while retrieving field values from more than one Data base table, into an internal table.

For the case below the data retrieved from resb and makt table into an internal table it_resb.

SELECT resb~rsnum

resb~rspos

resb~rsart

resb~matnr

resb~werks

resb~bdter

resb~baugr

resb~plnum

resb~bdmng

resb~meins

makt~maktg

INTO TABLE it_resb

FROM resb INNER JOIN makt ON resb_{matnr EQ makt}matnr

WHERE resb~matnr EQ it_mara-matnr

AND werks IN s_werks

AND bdter IN s_bdter.

Regards,

Rama Murthy.P

Re: Inner join

Former Member — Mon, 10 Sep 2007 05:26:01 GMT

Hi,

check this example.

REPORT ZSK005.

TABLES : LIKP.

DATA : BEGIN OF I_LIKP_LIPS OCCURS 0,

VBELN LIKE LIKP-VBELN,

LFART LIKE LIKP-LFART,

LFDAT LIKE LIKP-LFDAT,

VGBEL LIKE LIPS-VGBEL,

VGPOS LIKE LIPS-VGPOS,

END OF I_LIKP_LIPS.

SELECT-OPTIONS : S_VBELN FOR LIKP-VBELN OBLIGATORY MATCHCODE OBJECT VMVA

START-OF-SELECTION.

SELECT LIKP_{VBELN LIKP}LFART LIKP_{LFDAT LIPS}VGBEL LIPS~VGPOS

INTO TABLE I_LIKP_LIPS

FROM LIKP INNER JOIN LIPS

ON LIKP_{VBELN = LIPS}VBELN

WHERE LIKP~VBELN IN S_VBELN.

IF SY-SUBRC <> 0.

MESSAGE E001(ZX).

ENDIF.

END-OF-SELECTION.

LOOP AT I_LIKP_LIPS.

WRITE : / I_LIKP_LIPS-VBELN,I_LIKP_LIPS-LFART,I_LIKP_LIPS-LFDAT,

I_LIKP_LIPS-VGBEL,I_LIKP_LIPS-VGPOS.

ENDLOOP.