467,185 Members | 1,207 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

multiple outer join

Hi,

when I try a left outer join on one table everything works fine:
select * from (tourist.users u left outer join
tourist.user_extended_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_extended_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
Apr 21 '06 #1
  • viewed: 10063
Share:
1 Reply
Andreas Bauer wrote:
Hi,

when I try a left outer join on one table everything works fine:
select * from (tourist.users u left outer join
tourist.user_extended_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_extended_info ue on (u.id = ue.id)) t1 left outer join
tourist.profile_for_user p on t1.id = p.id


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

SELECT *
FROM ( tourist.users u LEFT OUTER JOIN
tourist.user_extended_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
Apr 21 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by p175 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.