By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

multiple outer join

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.