multiple outer join

[Andreas Bauer]

Hi,

when I try a left outer join on one table everything works fine:
select * from (tourist.users u left outer join
tourist.user_ex tended_info ue on (u.id = ue.id))

But now I need to do another left outer join with a third table, but
this doesn't work (although I found a tutorial on sql where it was
described that way):

select * from (tourist.users u left outer join
tourist.user_ex tended_info ue on (u.id = ue.id)) t1 left outer join
tourist.profile _for_user p on t1.id = p.id

Toad gives me this error message:
ERROR [42601] [IBM][DB2/NT] SQL0104N Auf "" folgte das unerwartete
Token "JOIN". Zu den möglichen Token gehören: "FROM". SQLSTATE=42601

Any hints on that?

Best regards,

Andi

[Knut Stolze]

Re: multiple outer join

Andreas Bauer wrote:
[color=blue]
> Hi,
>
> when I try a left outer join on one table everything works fine:
> select * from (tourist.users u left outer join
> tourist.user_ex tended_info ue on (u.id = ue.id))
>
> But now I need to do another left outer join with a third table, but
> this doesn't work (although I found a tutorial on sql where it was
> described that way):
>
> select * from (tourist.users u left outer join
> tourist.user_ex tended_info ue on (u.id = ue.id)) t1 left outer join
> tourist.profile _for_user p on t1.id = p.id[/color]

The correlation name "t1" cannot be used there. This will do:

SELECT *
FROM ( tourist.users u LEFT OUTER JOIN
tourist.user_ex tended_info ue on (u.id = ue.id) ) LEFT OUTER JOIN
tourist.profile _for_user p on ue.id = p.id

The thing is that the columns in the table produced by the first join do not
have to have unique names. So this is not a valid table in this respect.
If you need to refer to one such column, just use the correlation name of
the table from inside the first join.

Another example:

$ db2 "create table t ( a int, b int )"
$ db2 "select * from ( t t1 left outer join t t2 on t1.a = t2.b ) left outer
join t t3 on t1.a = t3.b"

A B A B A B
----------- ----------- ----------- ----------- ----------- -----------

0 record(s) selected.

(I don't have any data in the table.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany