topic Re: Join several tables in Application Development and Automation Discussions

Join several tables

Former Member — Tue, 05 Dec 2006 15:00:03 GMT

Hi,

I need a guidance to join 3 tables at the same time.

Best regards.

Re: Join several tables

Former Member — Tue, 05 Dec 2006 15:02:24 GMT

If you search this forum for "JOIN", you should find many examples.

Rob

Re: Join several tables

Former Member — Tue, 05 Dec 2006 15:03:37 GMT

check this example this might help,

SELECT a_{werks a}budat a_{grund a}ism03 a_{aufnr a}rueck a~rmzhl

a_{stzhl a}erzet

b_{matnr c}verid INTO CORRESPONDING FIELDS OF TABLE itab FROM

afru AS a INNER JOIN afpo AS b ON

a_{aufnr = b}aufnr INNER JOIN

mkal AS c ON b_{matnr = c}matnr

FOR ALL ENTRIES IN it_blpk

WHERE c~verid = it_blpk-verid

AND a~budat = it_blpk-budat

AND a~erzet = it_blpk-uzeit

AND a~werks = it_blpk-werks

AND b~matnr = it_blpk-matnr

AND a~grund IN s_grund AND

( a_{budat >= c}adatu AND

a_{budat <= c}bdatu ) AND

a~ism03 <> 0 AND

a~ile03 EQ 'STD'.

This statements joind data from 3 tables into one internal table.

Hope this helps.

Re: Join several tables

Former Member — Tue, 05 Dec 2006 15:03:38 GMT

same as joining two tables ...

after joining the first two tables ... add the condition for joining the 2nd and 3rd table ...

Cheers,

amit

Re: Join several tables

Former Member — Tue, 05 Dec 2006 15:04:50 GMT

select a_{field1 a}field2 b_{field3 b}field4 c_{field5 c}field6 into itab

from table1 as a inner join table2 as b on a-key = b-key

inner join table3 as c on b-key = c-key

where <conditions>..........Here while putting conditions need to put as a_{field or b}field or c~field.

Message was edited by:

Ramesh Babu Chirumamilla

Re: Join several tables

Former Member — Tue, 05 Dec 2006 15:18:22 GMT

You can use a dirty trick -:P SQ02...Create a query, make the join and copy the code -;)

Greetings,

Blag.

Re: Join several tables

Former Member — Wed, 06 Dec 2006 16:11:08 GMT

Hi Said ,

data: begin of i_kna1 occurs 1,

kunnr like kna1-kunnr,

land1 like kna1-land1,

name1 like kna1-name1,

ort01 like kna1-ort01,

end of i_kna1.

data: begin of i_vbak occurs 1,

kunnr like vbak-kunnr,

vbeln like vbak-vbeln,

erdat like vbak-erdat,

ernam like vbak-ernam,

end of i_vbak.

data: begin of i_vbap occurs 1,

vbeln like vbap-vbeln,

posnr like vbap-posnr,

matnr like vbap-matnr,

charg like vbap-charg,

end of i_vbap.

data: begin of i_mara,

matnr like mara-matnr,

mbrsh like mara-mbrsh,

mtart like mara-mtart,

meins like mara-meins,

end of i_mara.

data: begin of i_dest occurs 1,

kunnr like kna1-kunnr,

land1 like kna1-land1,

name1 like kna1-name1,

ort01 like kna1-ort01,

vbeln like vbak-vbeln,

erdat like vbak-erdat,

ernam like vbak-ernam,

posnr like vbap-posnr,

matnr like vbap-matnr,

charg like vbap-charg,

mbrsh like mara-mbrsh,

mtart like mara-mtart,

meins like mara-meins,

end of i_dest.

select k_{kunnr k}land1 k_{name1 k}ort01 v_{vbeln v}erdat v_{ernam p}posnr

p_{matnr p}charg into table i_dest from kna1 as k inner join vbak as v on

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

mara as m on p_{matnr = m}matnr.

loop at i_dest.

write: / i_dest-kunnr, i_dest-land1, i_dest-name1, i_dest-ort01,

i_dest-vbeln, i_dest-erdat, i_dest-ernam, i_dest-posnr, i_dest-matnr,

i_dest-charg, i_dest-mbrsh, i_dest-mtart, i_dest-meins.

endloop.

Cheers.

Re: Join several tables

Former Member — Wed, 06 Dec 2006 16:17:54 GMT

Hi,

U may experience performance related problem if u join more than 2 tables ( depends on table size ). For better performance u can join two tables get all the information in one internal table then considering that internal table as a driver table u can select all necessary data from the 3rd table using FOR ALL ENTRIES.

Cheers.