Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

multiple tables using left outer join?

 
rick collette
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, guys:

I have 4 tables: A, B, C; if I want to do the following:
A left outer join B, its result left outer join C.
How can I do that? BTW, I am using DB2.

The following is a sample inner join:

select a.id, b.first, c.last
from A as a, B as b, C as c

thanks
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rick,

Firstly let me give you a caveat: I have no experience with DB2.

However, the ANSI syntax for what you want to achieve would be as follows:


A left outer join B, its result left outer join C.
How can I do that? BTW, I am using DB2.




I'm afraid I don't have an ANSI-compliant RDBMS to hand to test that example on, so you might need to tweak it a little, e.g. remove the brackets.

Another word of warning: when you start to try outer joining to the result of an outer join, or the outer table in another outer join, things get a little iffy. I'm sure I remember Oracle 8i throwing up an error when I tried to do that a couple of years ago. More recently on Sybase 12.5, however it did not. I can't vouch for how DB2 will handle it but give it a go and let me know.

I hope you find this useful.

Regards

Jules
 
rick collette
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Julian:

It works. Sorry I gave you a late reply (I was out of town for a
week).

regards,


Originally posted by Julian Kennedy:
Hi Rick,

Firstly let me give you a caveat: I have no experience with DB2.

However, the ANSI syntax for what you want to achieve would be as follows:



I'm afraid I don't have an ANSI-compliant RDBMS to hand to test that example on, so you might need to tweak it a little, e.g. remove the brackets.

Another word of warning: when you start to try outer joining to the result of an outer join, or the outer table in another outer join, things get a little iffy. I'm sure I remember Oracle 8i throwing up an error when I tried to do that a couple of years ago. More recently on Sybase 12.5, however it did not. I can't vouch for how DB2 will handle it but give it a go and let me know.

I hope you find this useful.

Regards

Jules
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic