Join returns unexpected empty result

Question

I hope you can help me.

Ι have 2 tables ( eg. T1, T2 ):

• T1: ID (key), maktx, ...
• T2: ID (key), tabname (key), fieldname (key), fieldvalue, ...

T1 and T2 contain respectively 2 and 3 rows:

T1:             T2:
   ID MAKTX        ID TABNAME FIELDNAME FIELDVALUE
   -- -----        -- ------- --------- ----------
   1  text1        1  X       MATNR     MATNR1
   2  text2        2  X       WERKS     WERKS2
                   2  X       LGORT     LGORT2

The SQL code should output this:

ID1 MAKTX ID2 TABNAME FIELDNAME FIELDVALUE
--- ----- --- ------- --------- ----------
1   text1 1   X       MATNR     MATNR1
2   text2 2   X       WERKS     WERKS2

Below the code I tried:

    ir_id    = VALUE #( ).
    ir_matnr = VALUE #( sign = 'I' option = 'EQ' ( low = 'MATNR1' ) ).
    ir_werks = VALUE #( sign = 'I' option = 'EQ' ( low = 'WERKS2' ) ).
    SELECT *
        FROM T1
        JOIN T2 on T1~id = T2~id
        INTO CORRESPONDING FIELDS OF table <et_result>
        WHERE T1~id IN ir_id[]
        AND   ( T2~fieldname = 'MATNR' AND T2~fieldvalue IN ir_matnr[] )
        AND   ( T2~fieldname = 'WERKS' AND T2~fieldvalue IN ir_werks[] )

T1 and T2 have different structures, but share the same ID.

My problem is that it returns nothing. I don't know how to solve this problem.

P.S. I'm not the creator of this system, and can not perform any structural change on it, but just trying to solve this problem.

Any help would be appreciated.

Answer 1

A field cannot have two different values at the same time (MATNR and WERKS), so the AND condition has to be changed into OR:

SELECT *
    FROM T1
    JOIN T2 on T1~id = T2~id
    INTO CORRESPONDING FIELDS OF table <et_result>
    WHERE T1~id IN ir_id[]
    AND  ( ( T2~fieldname = 'MATNR' AND T2~fieldvalue IN ir_matnr[] )
    OR     ( T2~fieldname = 'WERKS' AND T2~fieldvalue IN ir_werks[] ) ).