abap

How to join 1 column of table 1 with table 2, and another one with table 2 again


I have two tables, like below: Table1

VBELN STAT1 STAT2
1 A B
2 B C

Table2

STATS DESC
A This is A
B This is B
C This is C

I am trying to derive the description for each Stat and populate as a single row: Expected results:

VBELN STAT1 STAT1DESC STAT2 STAT2DESC
1 A This is A B This is B
2 B This is B C This is C

I tried combining both using INNER JOIN but I am not sure how to fulfill it at one-go:

VBELN STAT1 STAT1DESC STAT2 STAT2DESC
1 A This is A B
2 B This is B C

How do I achieve the expected result using JOINS?


Solution

  • The second table has to be JOINed twice using different aliases. You did not provide the names of the tables, so I will just call the first table STATUS (vbeln, stat1, stat2), the second one TEXT (status, desc):

    SELECT FROM status
           INNER JOIN text AS text_1
                 ON status~stat1 = text_1~stats
           INNER JOIN text AS text_2
                 ON status~stat2 = text_2~stats
           FIELDS status~vbeln, status~stat1, text_1~desc, status~stat2, text_2~desc
           WHERE  ...
           INTO TABLE @DATA(result).