topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Mon, 05 May 2008 15:08:01 GMT

sample report for inner join using two tables

Re: inner join

Former Member — Mon, 05 May 2008 15:09:46 GMT

look at

DEMO_SELECT_INNER_JOIN

Re: inner join

Former Member — Mon, 05 May 2008 15:10:47 GMT

  SELECT MKPF~BUDAT MKPF~CPUTM
     MSEG~MATNR MSEG~WERKS MSEG~EBELN 
      MSEG~EBELP  MSEG~ERFMG
           INTO CORRESPONDING FIELDS OF TABLE 
           W_DTL_INVOICE
           FROM MKPF INNER JOIN MSEG
                  ON  MKPF~MBLNR = MSEG~MBLNR
                  AND MKPF~MJAHR = MSEG~MJAHR
           WHERE MKPF~BUDAT > '20040721' AND
                 MSEG~BWART = '101'       AND
                 MSEG~BUKRS = '1733'.

Re: inner join

Former Member — Mon, 05 May 2008 15:11:45 GMT

SELECT TAB1~<F1>

TAB1~<F2>

TAB2~<F3>

TAB2~<F4>

INTO TABLE <IT>

FROM TAB1 INNER JOIN TAB2

ON TAB1_{<F1> = TAB2}<F1>

WHERE TAB1~<F1> = <...> AND/OR

..................

Re: inner join

Former Member — Mon, 05 May 2008 15:13:53 GMT

hi,

SELECT VBRP~ZZ_DIVISION

VBRP~VBELN

VBRP~POSNR

VBRP~LLAND_AUFT

VBRP~ARKTX

VBRP~FKIMG

VBRP~VGBEL

VBRP~KZWI1

VBRP~KZWI2

VBRP~KZWI3

VBRP~KZWI5

VBRP~KURSK

VBRK~KUNAG

VBRK~VBTYP

VBRK~KNUMV

VBRK~FKDAT

FROM VBRP

INNER JOIN VBRK ON ( VBRP_{VBELN = VBRK}VBELN )

INTO CORRESPONDING FIELDS OF TABLE IT_VBRP

WHERE VBRP~ZZ_DIVISION IN S_DIV

AND VBRK~VBTYP NE 'U'

AND VBRK~FKSTO = SPACE

AND VBRK~FKART NOT IN ('S1', 'S2', 'S3').

reward points if useful,

regards,

muralidhar.

Re: inner join

Former Member — Mon, 05 May 2008 15:15:31 GMT

hi,

SELECT a~matnr a~meins a~mtart d~maktx b~werks c~lgort 
c~labst INTO CORRESPONDING FIELDS OF TABLE IT_MATERIAL
FROM mara AS a
INNER JOIN MAKT AS D on a~matnr = d~matnr
inner join marc AS b
ON a~matnr = b~matnr
inner join MARD as c
ON B~MATNR = C~MATNR AND B~WERKS = C~WERKS
where A~MATNR IN S_MATNR and b~werks in s_werks.

regards,

madhumitha

Re: inner join

Former Member — Mon, 05 May 2008 16:43:34 GMT

hi check this..

report test .

tables: pa0002,pa0008,pa0021.

data: begin of it_final occurs 0,

pernr like pa0002-pernr,

begda like pa0002-begda,

endda like pa0002-endda,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

ansal like pa0008-ansal,

lga01 like pa0008-lga01,

bet01 like pa0008-bet01,

famsa like pa0021-famsa,

fanam like pa0021-fanam,

favor like pa0021-favor,

end of it_final .

select-options:s_pernr for pa0002-pernr.

select a~pernr

a~begda

a~endda

a~vorna

a~nachn

b~ansal

b~lga01

b~bet01

c~famsa

c~fanam

c~favor

from ( ( pa0002 as a inner join pa0008 as b on a_{pernr = b}pernr )

inner join pa0021 as c on a_{pernr = c}pernr and b_{pernr = c}pernr )

into table it_final

where a~pernr in s_pernr.

sort it_final by pernr begda descending.

loop at it_final.

write:/ it_final-pernr,

it_final-begda,

it_final-endda,

it_final-vorna,

it_final-nachn,

it_final-ansal,

it_final-lga01,

it_final-bet01,

it_final-famsa,

it_final-fanam,

it_final-favor.

endloop.

regards,

venkat

Re: inner join

Former Member — Mon, 05 May 2008 16:45:18 GMT

Hi,

The Below Select statement is an example of Inner Join.

With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view.

SELECT stpo~stlnr stpo~idnrk mast~matnr mara~mtart stpo~menge  
INTO 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.

Pls reward if useful.

Thanks,

Sirisha.