topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Mon, 09 Jun 2008 08:49:16 GMT

i want to select data from 3 different tables using join.

these tables have a common filed

how an we do it

can u give the syntax

Re: inner join

Former Member — Mon, 09 Jun 2008 08:52:02 GMT

Hi,

Please try to help yourself first - look at the help at help.sap.com, press the F1 key on your keyboard and finally search SDN before posting. This has been discussed lots of times before.

Gareth.

Re: inner join

Former Member — Mon, 09 Jun 2008 08:52:13 GMT

Hi ,

See below example code for joining three tables. it may give idea about joining three tables.

tables : kna1,

vbak,

vbap.

data : begin of itab occurs 10,

kunnr type kunnr,

name1 type name1,

land1 type land1,

vbeln type vbeln,

erdat type erdat,

netwr type netwr,

posnr type posnr,

matnr type matnr,

kwmeng type kwmeng,

vrkme type vrkme,

end of itab.

select-options : s_kunnr for kna1-kunnr,

s_vbeln for vbak-vbeln.

start-of-selection.

select k_{kunnr k}name1 k_{land1 v}vbeln v_{erdat v}netwr p_{posnr p}matnr p_{kwmeng p}vrkme

into table itab

from kna1 as k inner join vbak as v

on k_{kunnr = v}kunnr inner join vbap as p

on v_{vbeln = p}vbeln

where k~kunnr in s_kunnr

and v~vbeln in s_vbeln.

*break xverinon02.

if sy-subrc NE 0.

message 'Not found record for given selection' type 'E'.

endif.

top-of-page.

uline.

write:/40 'CUSTOMER SALES ORDER WITH ITEM DETAILS' color 4.

uline.

end-of-page.

uline.

write:/10 'End of Page',60 'Page No.',sy-pagno.

end-of-selection.

sort itab.

loop at itab.

at new kunnr.

write:/ itab-kunnr.

endat.

write:/10 itab-name1, itab-land1, itab-vbeln, itab-erdat, itab-netwr, itab-posnr, itab-matnr, itab-kwmeng, itab-vrkme.

endloop.

Regards,

Vishvesh

if helpful, rewards it.

Re: inner join

Former Member — Mon, 09 Jun 2008 08:53:31 GMT

hi there...

folow the following select query demonstrating inner join on three tables...

SELECT vbak_{vbeln vbfa}erdat INTO (itab-vbeln, itab-wadat)

FROM ( vbak INNER JOIN vbap

ON vbap_{vbeln = vbak}vbeln )

INNER JOIN vbfa ON vbak_{vbeln = vbfa}vbelv

WHERE vbak~kunnr = m_wm AND

vbak~vbtyp = 'C' AND

vbfa~erdat IN s_date AND

vbap~matnr = 'MZ1807F' AND

vbfa_{posnv = vbap}posnr AND

vbfa~vbtyp_n = 'J'.

do reward if helpful.

Re: inner join

Former Member — Mon, 09 Jun 2008 08:55:36 GMT

hi,

select t1~f1

t1~f2

t2~f3

t2~f4

t3~f5

t3~f6

into table itab

from <dbtab1> as t1 inner join

<dbtab2> as t2

on t1~<commonfield> = t2`<commonfield>

inner join <dbtab3 > as t3

on t1~<commonfield> = t3<commonfield>

where <conditions>.

hope it helps