JOIN in select query

[Former Member]

Hi All,

I need to do an enhancement in the following Select query.

SELECT

• MARA data

a~matnr

• MARC data

b~werks

• MARA data

a~normt

a~ean11

a~labor

a~mstae

a~mstde

a~bismt

a~spart

a~zzsc

a~zzmc

a~zzpc

a~zzbu

a~zz4tpc

a~zz6tpc

a~kosch

a~iprkz

g~bmatn

a~normt

g~mfrnr

a~bstme

a~zeifo

a~ersda

• MARC data

b~mtvfp

b~sernp

b~herkl

b~ekgrp

b~dispo

b~insmk

b~disgr

b~ladgr

b~lgfsb

b~mtver

b~perkz

b~eprio

b~wzeit

• MVKE data

c~dwerk

c~kondm

c~bonus

c~mtpos

c~provg

c~mvgr1

c~mvgr2

c~mvgr3

c~prat9

c~prat1

• MBEW data

d~bklas

d~stprs

INTO TABLE i_materials

FROM ( marc as b

inner jOIN mara AS a ON a_{matnr = b}matnr

inner JOIN mvke AS c ON c_{matnr = a}matnr

inner JOIN ampl AS g ON g_{bmatn = a}matnr

LEFT OUTER JOIN mbew AS d ON d_{matnr = b}matnr

AND d_{bwkey = b}werks )

WHERE a~matnr IN s_matnr

AND a~normt IN s_normt

AND a~mstae IN s_mstae

AND a~zz4tpc IN s_zz4tpc

AND a~zz6tpc IN s_zz6tpc

AND a~bismt IN s_bismt

AND a~iprkz IN s_iprkz

AND a~spart IN s_spart

AND a~zzsc IN s_zzsc

AND a~zzpc IN s_zzpc

AND a~zzmc IN s_zzmc

AND a~zzbu IN s_zzbu

AND a~ersda IN s_ersda

• MARC Data

AND b~werks IN s_werks

AND b~ekgrp IN s_ekgrp

AND b~disgr IN s_disgr

AND b~perkz IN s_perkz

AND b~sernp IN s_sernp

AND b~ladgr IN s_ladgr

AND b~mtvfp IN s_mtvfp

AND b~lgfsb IN s_lgfsb

AND b~mtver IN s_mtver

AND b~eprio IN s_eprio

AND b~wzeit IN s_wzeit

AND b~herkl IN s_herkl

• MVKE data

AND c~mtpos IN s_mtpos

AND c~kondm IN s_kondm

AND c~bonus IN s_bonus

AND c~provg IN s_provg

AND c~mvgr1 IN s_mvgr1

AND c~mvgr2 IN s_mvgr2

AND c~mvgr3 IN s_mvgr3

• AMPL Data

AND g~reoam <> 'X'.

This query will pick the materials only if it is maintained across all the tables MARA,MARC,MVKE,AMPL,MBEW.

Now the requirement is, the program should pick the materials that are maintained in MARA table, doesn't matter if the material is not maintained in the other tables.

Please tell me how to modify this Select query. I dont have any idea on JOINs.

Thanks in Advance,

Neethu.

[Former Member]

Select from the table MARA first and then use a LEFT OUTER JOIN on all the other tables (MARC, MVKE...)

[Former Member]

Hi Manoj,

If possible please tell me how to use left outer join in the select query. Please provide the query

Thanks,

Neethu

[Former Member]

You can use this sample piece of code with suitable modifications:

SELECT
<fields...>
FROM mara AS a
LEFT OUTER JOIN marc AS b
ON a~matnr EQ b~matnr
LEFT OUTER JOIN mvke AS c
ON a~matnr EQ c~matnr
...

[Former Member]

Hi Manoj,

I have modified the query like

select fields

FROM ( mara as a

left outer join marc AS b ON a_{matnr = b}matnr

left outer Join mvke AS c ON a_{matnr = c}matnr

left outer Join ampl AS g ON a_{matnr = g}bmatn

left outer Join mbew AS d ON a_{matnr = d}matnr )

WHERE a~matnr IN s_matnr

AND a~normt IN s_normt

AND a~mstae IN s_mstae

AND a~zz4tpc IN s_zz4tpc

AND a~zz6tpc IN s_zz6tpc

AND a~bismt IN s_bismt

AND a~iprkz IN s_iprkz

AND a~spart IN s_spart

AND a~zzsc IN s_zzsc

AND a~zzpc IN s_zzpc

AND a~zzmc IN s_zzmc

AND a~zzbu IN s_zzbu

AND a~ersda IN s_ersda

• MARC Data

AND b~werks IN s_werks

AND b~ekgrp IN s_ekgrp

AND b~disgr IN s_disgr

AND b~perkz IN s_perkz

AND b~sernp IN s_sernp

AND b~ladgr IN s_ladgr

AND b~mtvfp IN s_mtvfp

AND b~lgfsb IN s_lgfsb

AND b~mtver IN s_mtver

AND b~eprio IN s_eprio

AND b~wzeit IN s_wzeit

AND b~herkl IN s_herkl

• MVKE data

AND c~mtpos IN s_mtpos

AND c~kondm IN s_kondm

AND c~bonus IN s_bonus

AND c~provg IN s_provg

AND c~mvgr1 IN s_mvgr1

AND c~mvgr2 IN s_mvgr2

AND c~mvgr3 IN s_mvgr3

• AMPL Data

AND g~reoam <> 'X'.

I am getting the error, "No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE condition:"B~WERKS".

Now prob. is with WHERE condition. If you know the solution please let me know.

[Former Member]

remove the 'Joins'

and remove the fields no longer required from teh where clause ex any field other than A~.

[Sandeep_Panghal]

You can directly write a simple select on MARA with WHERE clause .