inner join 4 tables

[Shahid]

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.

[varma_narayana]

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>

[Former Member]

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á

[Former Member]

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.

[Former Member]

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

[Former Member]

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