Skip to Content
Former Member
Apr 15, 2009 at 01:10 PM

Left outer join 3 tables with where-statement


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.