Join on select for MARA, MARC, MAKT, MLAN

[Former Member]

Hiya,

I'm trying joins out rather than looping through internal tables (yes I know I'm an ABAP dinosaur).

Anyway I'm trying to join MARA, MARC, MAKT, MLAN.

Where we are aiming to extract all the plant data(MARC) along with the relevant details from the other details.

I've come up with:

  SELECT

  mara~mandt

  mara~matnr

  mara~mtart

  mara~matkl

  marc~werks

  makt~maktx

  mlan~taxm1

  mlan~taxm2

   INTO TABLE itab_data

  FROM  mara

     INNER JOIN marc

       ON marc~matnr = mara~matnr

     INNER JOIN mlan

       ON  mlan~matnr = marc~matnr

       AND mlan~aland = p_aland     

     INNER JOIN makt

       ON  makt~matnr = marc~matnr

       AND makt~spras = 'E'

   WHERE mara~matnr IN s_matnr

     AND mara~matkl IN s_mtart

     AND marc~werks IN s_werks.

Would that seem okay to you experts?

[Ashg1402]

Hi,

make a small change, put makt~spras = 'E' AND mlan~aland = p_aland   in where clause

[Patrick_vN]

I don't see why this select wouldn't work.

Any particular reason you select MANDT? And personally I'd link MLAN to MARA as well (instead of MARC), but I doubt that would make enormous changes

Generally I tend to refrain from putting references to other variables into the join statements (and instead add them to the where clause).

Nitpicking:

Oh, and 'all plant data'? You're only selecting the plant from MARC, and I seem to remember there are a few more fields..

[Former Member]

Thanks for the helpful and funny reply.

I've only shown some of the fields I'm selecting as its the join that I'm most interested in.

Out of interest why would a join on MARA be better?

I thought of adding ALAND and SPRAS into the join statement as they are key fields for MLAN and MAKT. The report seemed to run a little faster that way, or maybe I'm imagining things.

[Former Member]

Hi

I understand you want to confirm using JOINS the way you did is good to go, rather than FOR ALL ENTRIES and Internal tables.

Well, I personally do not think there is anything wrong with this select query.

Since Material (field matnr) is common in all these tables, with MARA being the Master table, I agree with Patrick that all joins should use MARA-MATNR as key.

As far as other conditions (where clause) are concerned, you would need to write them based on your requirement.

Technically, this select query is good to go.

All the Best !!!

Thanks and Regards,

Sheily Babel.

[Former Member]

Thanks everyone.

Following your advice I've adjusted the join to be:

   INTO TABLE itab_data

  FROM  mara

     INNER JOIN marc

       ON marc~matnr = mara~matnr

     INNER JOIN mlan

       ON  mlan~matnr = mara~matnr

*       AND mlan~aland = p_aland      "key field

     INNER JOIN makt

       ON  makt~matnr = mara~matnr

*       AND makt~spras = 'E'

   WHERE mara~matnr IN s_matnr

     AND mara~matkl IN s_mtart

     AND marc~werks IN s_werks

     AND mlan~aland = p_aland

     AND makt~spras = 'E'.