...

View Full Version : Table Relationships and Left Outer joins



stuart010
11-15-2011, 11:56 AM
Hi all,

I have a bit of an issue relating to combining multiple tables.

Overview
I have a total of 5 tables
1.Applicants
2.Appbankdetails
3.AppltdAddr
4.PersonalDetails
5.UmbrellaCompany

Tables 2,3,4 are left outer joined to table 1 through a common field; My stumbling block is that I need to combine table 5, however a common field only exists between 4 and 5.

I have searched extensively for an answer but I have maybe not phrased my question correctly and would be most grateful if someone could assist. My code so far:-



SELECT *

FROM
ITRIS.dbo.Applicants Applicants

LEFT OUTER JOIN ITRIS.dbo.AppBankDetails AppBankDetails
ON
Applicants.APP_ID = AppBankDetails.APP_ID

LEFT OUTER JOIN ITRIS.dbo.AppLtdAddr AppLtdAddr
ON
Applicants.APP_ID = AppLtdAddr.APP_ID

LEFT OUTER JOIN ITRIS.dbo.PersonalDetails PersonalDetails
ON
Applicants.APP_ID = PersonalDetails.APP_ID

LEFT OUTER JOIN ITRIS.dbo.PersonalDetails PersonalDetails
ON
UmbrellaCompany.ID = PersonalDetails.UMBRELLA_COMPANY_ID

From the above the issue I have relates to the final outer join, should I be using a different syntax?

Regards

Stuart

guelphdad
11-15-2011, 01:57 PM
please provide some sample rows from all the tables so we can see how the data should relate.

Old Pedant
11-15-2011, 11:45 PM
No, the last join is wrong. And you should never use SELECT * when joining tables, as if any fields have the same name you will have a conflict in the output


SELECT specific, list, of, fields
FROM ITRIS.dbo.Applicants Applicants
LEFT OUTER JOIN ITRIS.dbo.AppBankDetails AppBankDetails
ON Applicants.APP_ID = AppBankDetails.APP_ID
LEFT OUTER JOIN ITRIS.dbo.AppLtdAddr AppLtdAddr
ON Applicants.APP_ID = AppLtdAddr.APP_ID
LEFT OUTER JOIN ITRIS.dbo.PersonalDetails PersonalDetails
ON Applicants.APP_ID = PersonalDetails.APP_ID
LEFT OUTER JOIN ITRIS.dbo.UmbrellaCompany UmbrellaCompany
ON UmbrellaCompany.ID = PersonalDetails.UMBRELLA_COMPANY_ID



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum