Combine three tables with JOIN

Question

I am facing the problem where I have three tables:
First table has data like:

A B C D  
1 2 3 4  
1 2 3 5

Second table has data like:

D E  
4 x  
6 y

Third table has data like:

E F  
x result1
y result2

What i need is a Query that displays all Values from the first table (obviously a left outer join) and if available the combination of the second and third table.

So the result would look somewhat like this:

A B C D E F  
1 2 3 4 x result 1  
1 2 3 5

However I cannot get a solution on this.

I already tried left joining two times (results in an error as it is not allowed in opensql) and inner joining the second and third table and trying to right join the first table (also not allowed in opensql).

How do I realize my desired result-table?

Florian Florian · Accepted Answer · 2018-08-09T18:10:28

SELECT *
  FROM first
  LEFT OUTER JOIN second
    ON second~d = first~d
  LEFT OUTER JOIN third
    ON third~e = second~e
  INTO TABLE @DATA(lt_result).

Sure you can do multiple LEFT OUTER JOINs in OpenSQL.

The second join might also be an INNER JOIN, depending on what precisely you want.

Verified on a SAP NetWeaver 7.52 on SAP HANA 2.0.

Combine three tables with JOIN

1 Answers