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

Question

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?

Answer 1

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