OUTER JOIN MORE THAN 2 TABLES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

OUTER JOIN MORE THAN 2 TABLES

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-06-07 : 15:36:30

Is it possible to do a full outer join on more than 2 tables, i've been looking for the syntax online but can't seem to find it, so far i have

FROM dbo.TRNEWL_RTENTN_DETL RR
    FULL OUTER JOIN VBVT_FIN_DIV_TRANSF
          ON RR.X_POLICY_NO = VBVT_FIN_DIV_TRANSF.X_POLICY_NO and 
 RR.X_POLICY_EFCTV_DT= VBVT_FIN_DIV_TRANSF.X_POLICY_EFCTV_DT and
 RR.X_ASCO_CD = VBVT_FIN_DIV_TRANSF.X_ASCO_CD and
 RR.PRODUCT_RENWL_ABBR = VBVT_FIN_DIV_TRANSF.PRODUCT_RENWL_ABBR

Now i need to do another Full outer join to RR with table VBVT_FIN_RGN_TRANSF, is that possible?

jpockets
Starting Member

45 Posts

Posted - 2007-06-07 : 15:54:03

I found some info on the web and tried it worked but when i put the column from the third table in the select table i get invalid column, am I doing this right code...

VBVT_FIN_DIV_TRANSF.APPROVED_ALL_DT,
    VBVT_FIN_DIV_TRANSF.TICKER_SYMBOL_CD,
    VBVT_FIN_DIV_TRANSF.REVENUE_AM,
    VBVT_FIN_DIV_TRANSF.COMMENTS,
    VBVT_FIN_RGN_TRANSF.IN_DIV_NO
    FROM dbo.TRNEWL_RTENTN_DETL RR
    FULL OUTER JOIN VBVT_FIN_DIV_TRANSF
          ON RR.X_POLICY_NO = VBVT_FIN_DIV_TRANSF.X_POLICY_NO and 
 RR.X_POLICY_EFCTV_DT= VBVT_FIN_DIV_TRANSF.X_POLICY_EFCTV_DT and
 RR.X_ASCO_CD = VBVT_FIN_DIV_TRANSF.X_ASCO_CD and
 RR.PRODUCT_RENWL_ABBR = VBVT_FIN_DIV_TRANSF.PRODUCT_RENWL_ABBR
 FULL OUTER JOIN VBVT_FIN_RGN_TRANSF  
    ON RR.X_POLICY_NO = VBVT_FIN_RGN_TRANSF.X_POLICY_NO

The column that says invalid column name is VBBY_FIN_RGN_TRANSF_DETL.IN_DIV_NO

does anybody have any ideas...

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 15:58:48

sure, you can

select *
from (tblA a full outer join tblB on a.id=b.id)
full outer join tblC c on a.id=c.id

parantheses are for clarity.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-07 : 16:00:23

I strongly recommend not to use a single full outer join, much less multiple ones. FULL OUTER JOINs make a huge mess and can be very difficult to work with and logically understand, plus every column can come back NULL. A single FULL OUTER JOIN is probably a mistake; multiple FULL OUTER JOIN are *definitely* a mistake! :)

see this for more:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

Read the article, step back a little and think logically about the data you want to return and how to approach it. If you need any help, just provide us with some specifics.

- Jeff
http://weblogs.sqlteam.com/JeffS

jpockets
Starting Member

45 Posts

Posted - 2007-06-07 : 16:19:48

Thanks for the reply, i read the article you linked but i'm not sure a union would work. This is what i'm trying to do:

I have one giant table almost 100 coulumns i have three child tables that i want to join to, these three tables have only 10 to 15 columns. The only thing the have in common are 4 columns that I'm using to join on. I need to select different columns from each. I was going to use 3 full outer joins to do this, but after reading that might not be the best way, i don't think an union would work, anybody have any ideas, this is the first time i worked in sql server threw me right into the fire on this one, but that's the only way you learn... thank-you for the help so far...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources