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.

It is so unclear what you are asking. If you use `*` then you do get all the fields from the join. — Jagger, Oct 22 '20 at 08:52
"fieldname" is the name of the field (if yes, the WHERE condition is not correct)? or the fields you want to select for are MATNR and WERKS? — József Szikszai, Oct 22 '20 at 08:54
I need to get all data from T2 to the ID from T1. @JózsefSzikszai Yes. Fieldname is the name of the field. I'm not sure whether the SQL statement will give me the correct data. — Silver Paw, Oct 22 '20 at 09:04
well a the value of a field cannot be WERKS and MATNR at the same time, you have to change the AND condition to OR: ...AND ( ( T2~fieldname = 'MATNR' AND T2~fieldvalue IN ir_matnr[] ) OR ( T2~fieldname = 'WERKS' AND T2~fieldvalue IN ir_werks[] ) ) — József Szikszai, Oct 22 '20 at 09:08
@JózsefSzikszai Thank you. To change the AND condition to OR solved my problem. I apologize for your wasted time on this triviality. — Silver Paw, Oct 22 '20 at 09:20
It is not a waste of time. Please post an answer to your own question it might help some other developers if they have the same problem. — Jagger, Oct 22 '20 at 09:45
I largely edited the question to make the answer coherent. I think the original requirement was to return all the T2 lines for each ID who satisfied the selection of MATNR and WERKS, i.e. probably `WHERE T1~ID IN ir_id[] AND EXISTS ( SELECT * FROM T2 WHERE ID = T1~ID AND ( ( fieldname = 'MATNR' AND fieldvalue IN ir_matnr[] ) OR ( fieldname = 'WERKS' AND fieldvalue IN ir_werks[] ) ) ).` would have been a better suited answer to the original question. — Sandra Rossi, Sep 18 '21 at 06:31

József Szikszai · Accepted Answer · 2020-10-22T18:10:22.120

3

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[] ) ).

edited Oct 22 '20 at 18:10

answered Oct 22 '20 at 09:54

József Szikszai

4,791
3
14
24

Join returns unexpected empty result

1 Answers1