Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Left outer join 3 tables with where-statement

Former Member
0 Kudos

Hi folks,

I hope you can understand (and maybe solve) my problem.

Generally I try to left outer join three tables. The third table is used for a WHERE-statement.

The three table structures are the following:

table 1 (user)

user1 | key

table 2 (detail)

key | ID

table 3 (header)

ID | user2

...and I want to achieve the following structure (as example filled with data):

user | key | ID

-


|-----|----

xy | a | 001

xy | b | #

z | b | #

The clue ist the usage of the third table. I need the table to set user1 and user2 equal (WHERE) but there are two problems:

1) Obviously I can't left outer join two tables with each other. In this case I already used the 'key' of table 1 to join it with the 'key' of table 2. So I can't left outer join the 'ID' of table 2 with the 'ID' of table 3. Error message that I can only left outer join a table once. Any proposals?

2) I have to include a WHERE to equal user1 with user2. But I am not allowed to use the user2 from table 3 because of the left outer join.

I tried this coding:

SELECT auser1 akey b~id INTO TABLE itab FROM ( table1 AS a

LEFT OUTER JOIN table2 AS b ON akey = bkey )

LEFT OUTER JOIN table3 AS c ON bID = cID )

WHERE auser1 = cuser2.

I would really appreciate your help.

Regards

MrclSpdl

1 ACCEPTED SOLUTION

Former Member
0 Kudos

IF you want to join a DB table with an internal table, you need to use the 'FOR ALL ENTRIES' statement.

select dbfields

into table itab2

from dbtab

for all entries in itab

where dbfield1 = itab-field1.

This will get you a second internal table with all the corresponding data for the first selection. You can then join them with a loop through the first table and a read table on the second table (for 1 - 1 relation) or a nested loop statement on both tables (for 1 - N relation). Make itab a hashed table when using read table with key, use a sorted table if you need to loop without key access.

Regards,

Freek

4 REPLIES 4

shahid_malayil1
Explorer
0 Kudos

Hi MrclSpdl,

You can make the join for first two table and get in to a internal table. Then join the internal table with the third table.

Best regards,

Shahid Malayil

0 Kudos

Hi Shahid,

I already tried to join an internal table filled with the data from the first select. Unfortunetaly it isn't possible to work with a SELECT ... FROM statement and internal tables in ABAP

Rgds

MrclSpdl

Former Member
0 Kudos

IF you want to join a DB table with an internal table, you need to use the 'FOR ALL ENTRIES' statement.

select dbfields

into table itab2

from dbtab

for all entries in itab

where dbfield1 = itab-field1.

This will get you a second internal table with all the corresponding data for the first selection. You can then join them with a loop through the first table and a read table on the second table (for 1 - 1 relation) or a nested loop statement on both tables (for 1 - N relation). Make itab a hashed table when using read table with key, use a sorted table if you need to loop without key access.

Regards,

Freek

0 Kudos

Thanks Freek! I tried it your way and it works totally fine.