topic Re: Left outer join 3 tables with where-statement in Application Development and Automation Discussions

Left outer join 3 tables with where-statement

Former Member — Wed, 15 Apr 2009 13:10:10 GMT

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 a_{user1 a}key b~id INTO TABLE itab FROM ( table1 AS a

LEFT OUTER JOIN table2 AS b ON a_{key = b}key )

LEFT OUTER JOIN table3 AS c ON b_{ID = c}ID )

WHERE a_{user1 = c}user2.

I would really appreciate your help.

Regards

MrclSpdl

Re: Left outer join 3 tables with where-statement

shahid_malayil1 — Wed, 15 Apr 2009 13:32:15 GMT

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

Re: Left outer join 3 tables with where-statement

Former Member — Wed, 15 Apr 2009 14:08:50 GMT

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

Re: Left outer join 3 tables with where-statement

Former Member — Tue, 05 May 2009 13:18:57 GMT

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

Re: Left outer join 3 tables with where-statement

Former Member — Tue, 05 May 2009 17:57:21 GMT

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