Join 3 tables

[Abapper0114]

Hello experts,

Can you help me join these 3 tables?

  SELECT ebeln
         bsart
    FROM ekko
    INTO TABLE it_ekko
   WHERE bsart = 'ZLSR'.

  IF it_ekko IS NOT INITIAL.
    SELECT ebeln,
           ebelp,
           matnr,
           werks,
           menge,
           bwtty,
           bwtar
      FROM ekpo
       FOR ALL ENTRIES IN @IT_ekko
     WHERE ebeln = @IT_ekko-ebeln
      INTO TABLE @IT_ekpo.

    IF it_ekpo IS NOT INITIAL.
      SELECT stprs,
             matnr,
             bwkey,
             bwtar,
             bwtty
        FROM mbew
         FOR ALL ENTRIES IN @IT_ekpo
       WHERE matnr = @IT_ekpo-matnr
         AND bwkey = @IT_ekpo-werks
        INTO TABLE @IT_mbew.

        IF sy-subrc = 0.
        ENDIF.
    ENDIF.
  ENDIF.

I've already tried this and it's not getting the right record. 

SELECT c~stprs,
       c~matnr,
       c~bwkey,
       c~bwtar,
       c~bwtty
  FROM ekko AS A
    INNER JOIN ekpo AS b
          ON a~ebeln = b~ebeln
    INNER JOIN mbew AS c
          ON c~matnr = b~matnr
         AND c~bwkey = b~werks
  WHERE a~bsart = 'ZLSR'
  INTO TABLE @DATA(it_temp).

 With using the first code sample IT_MBEW gets 41 records while using the 2nd code sample, IT_TEMP gets 112 records. 

[KjetilKilhavn]

What did you expect? (Hint: I think it is your expectation that is wrong, not the result.)

You're joining EKPO and MBEW on material number and plant/valuation area, so while the stand-alone selection from MBEW finds 41 unique combinations of MATNR and BWKEY the joined selection finds that these 41 combinations are used in 112 purchase order items.

But there is possibly something else that isn't right here. You have a join that assumes that the valuation area for a plant has an ID that is exactly the same as the ID of the plant itself. Perhaps that is true, but perhaps it isn't. You should in principle join via the plant master data table T001W to ensure you get the correct number of records and the correct values.

[Abapper0114]

Hello guys. I will be closing this thread. i already got the solution. I just deleted the duplicates. Thank you.

[RaymondGiuseppi]

You can add T001W so that your code will also other options of Customizing (as suggested by @KjetilKilhavn.

But also check for MM Customizing : Split Valuation, you can get multiple valuation per plant (e.g. new/used and a total record) and you don't provide enough fields in the join.

• I ususally add a "bwtty = space OR bwtar <> space" clause to remove duplicates in some MM reports.
• Note that BWTAR and BWTTY are also in table EKPO but you don't use them
• Use STPRS when VPRSV is 'S' and VERPR when 'V' 

What exactly are you looking for in MBEW?