topic Re: inner join 4 tables in Application Development and Automation Discussions

inner join 4 tables

Shahid — Fri, 24 Aug 2007 11:37:33 GMT

can anyone give example how to join 4 tables?

SELECT mara~matnr werks xchar mtart matkl meins trame umlmc

INTO CORRESPONDING FIELDS OF TABLE t_mat

FROM mara INNER JOIN marc

ON mara_{matnr = marc}matnr

WHERE mara~matnr IN matnr

AND werks IN werks

AND mtart IN matart

AND matkl IN matkla

AND ekgrp IN ekgrup

AND mara~lvorm = lvorm.

select mblnr mjahr matnr werks

from mseg

into table it_mseg

for all entries in t_mat

where matnr = t_mat-matnr

and werks = t_mat-werks.

select mblnr mjahr budat

from mkpf

into table it_mkpf

for all entries in it_mseg

where mblnr = it_mseg-mblnr

and mjahr = it_mseg-mjahr.

i m adding additional field in the selection screen "BUDAT FROM MKPF'..

so look at the above code, how i wrote to get the details of bkpf.

so now i just want one select statement where it picks mara, marc fields along with mkpf-budat.

Re: inner join 4 tables

varma_narayana — Fri, 24 Aug 2007 11:40:46 GMT

Hii Shahid...

Never join more than 3 tables .. Performance will be very poor.

Instead you can create separate internal tables and use FOR ALL ENTRIES..

So your code is better..

And one more thing

Avoind using

INTO CORRESPONDING FIELDS OF TABLE t_mat (this takes lot of time)

instead use

INTO TABLE t_mat .

<b>Reward if Helpful</b>

Re: inner join 4 tables

Former Member — Fri, 24 Aug 2007 11:41:27 GMT

Hi!

select mblnr mjahr budat

from mkpf

into table it_mkpf

for all entries in it_mseg

where mblnr = it_mseg-mblnr

and mjahr = it_mseg-mjahr

AND budat IN s_budat. "<<<

And don't forget to loop on your it_mkpf first, because it is your shortes table. It may not contain entries, the above tables do.

Regards

Tamá

Re: inner join 4 tables

Former Member — Fri, 24 Aug 2007 11:42:38 GMT

Hi,

Joining more than 3 tables is not advisable, so try to create a veiw in SE11 for the same with the same conditions and use it in ur program.

Regards,

Padmam.

Re: inner join 4 tables

Former Member — Sat, 25 Aug 2007 15:18:13 GMT

Consider these 4 tables. MARA , MARC , MARD , MAKT.

then select query will be to join all these 4 tables.

Select A_{MATNR A}MTART B_{WERKS C}EINME C_{SPEME D}MAKTX

FROM MARA AS A INNER JOIN MARC AS B ON

A_{MATNR = B}MATNR INNER JOIN MARD AS C ON

B_{MATNR = C}MATNR INNER JOIN MAKT AS D ON

C_{MATNR = D}MATNR INTO CORRESPONDING FIELDS OF TABLE

ITAB WHERE A~MATNR IN SO_MATNR.

Note : SO_MATNR is the variable of selection screen.

Using these select query you can get the field from all 4 tables.

Pradip Pawar.

give points if answer is valuable

Re: inner join 4 tables

Former Member — Sat, 25 Aug 2007 15:24:07 GMT

hi consider we want to retrieve data from these 4 tables MARA , MARC ,MARD , MAKT........Then our select query will be like this.

SELECT A_{MATNR A}MTART B_{WERKS C}LABST D~MAKTX FROM

MARA AS A INNER JOIN MARC AS B ON

A_{MATNR = B}MATNR INNER JOIN MARD AS C ON

B_{MATNR = C}MATNR INNER JOIN MAKT AS D ON

C_{MATNR = D}MATNR INTO CORRESPONDING FIELDS OF TABLE ITAB

WHERE A~MATNR IN SO_MATNR.

note : SO_MATNR will be variable in selection screen.

by using these you will get fields from all 4 tables.

Pradip Pawar.

Give points if ans is valuable