Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Relationships and Left Outer joins

    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:-


    Code:
    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    please provide some sample rows from all the tables so we can see how the data should relate.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    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
    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •