That works, thanks James! Yes, the t1, t2, etc doesn't evaluate the same does it? Luckily I don't have any "bad" common columns, but if you did want to pull a column from one particular table so that you can call it something else, I wonder how you would identify it within the "as select" part?
Karen -----Original Message----- From: James Bentley <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, Oct 30, 2013 10:27 am Subject: [RBASE-L] - Re: Left Outer Join Karen, You can but the proper syntax is: CREATE VIEW vContactAll AS SELECT listallcolsneeded + FROM (Contact t1 LEFT OUTER JOIN client t2 ON t1.id = t2.id) J1 + LEFT OUTER JOIN people t3 ON J1.id = t3.id I also suggest you modify the view creation to define include explicit name of columns in view creation as follows CREATE VIEW ContactAll (listallcolsneeded) AS SELECT listallcolsneeded ... the syntax of "t1.*, t2.*, t3.*" will not work as qualifying t1. or t2. etc is not what you think. Here is an example from one of my applications that works CREATE VIEW `Xp2MMNoLaAlum` (LACRollNumber,LACTXHqMbrType,LACFullNameFIL,+ LACFullNameLFM,LACSalutation,LACProfTitle,LACDearBroGreet,LACNickName,+ LACCHExtraAdrLine,LACCHMainAdrLine,LACCHCity,LACCHStateUSPSCode,+ LACCHPostalCode,LACCHCountry,LACCHPhoneNbr,LACCHCellNbr,LACCurEMailAdr,+ LACCWJobTitle,LACCWCompanyName,LACCWExtraAdrLine,LACCWMainAdrLine,+ LACCWCity,LACCWStateUSPSCode,LACCWPostalCode,LACCWCountry,LACCWPhoneNbr,+ LACCWFaxNbr,LACBirthDate,LACInitiated) + AS SELECT LACRollNumber,LACTXHqMbrType,LACFullNameFIL,LACFullNameLFM,+ LACSalutation,LACProfTitle,LACDearBroGreet,LACNickName,+ LACCHExtraAdrLine,LACCHMainAdrLine,LACCHCity,LACCHStateUSPSCode,+ LACCHPostalCode,LACCHCountry,LACCHPhoneNbr,LACCHCellNbr,LACCurEMailAdr,+ LACCWJobTitle,LACCWCompanyName,LACCWExtraAdrLine,LACCWMainAdrLine,+ LACCWCity,LACCWStateUSPSCode,LACCWPostalCode,LACCWCountry,+ LACCWPhoneNbr,LACCWFaxNbr,LACBirthDate,LACInitiated + FROM ((LACMbrRegister T1 LEFT OUTER + JOIN LACCurHomeInfo T2 ON T1.LACRollNumber = T2.LACRollNumber) J1 LEFT + OUTER JOIN LACCurWorkInfo T3 ON J1.LACRollNumber = T3.LACRollNumber) J2 LEFT + OUTER JOIN LACCurEmailInfo T4 ON J2.LACRollNumber = T4.LACRollNumber Jim Bentley, American Celiac Society 1-504-737-3293 -------------------------------------------- On Wed, 10/30/13, Karen Tellef <[email protected]> wrote: Subject: [RBASE-L] - Left Outer Join To: "RBASE-L Mailing List" <[email protected]> Date: Wednesday, October 30, 2013, 10:07 AM Trying something new. Can I use 2 left outer joins? Contact is my main table, may or may not be a matching ID in the Client and People table, but I'm getting a syntax error. It works fine with just t1 and t2, errors when I add in the t3 syntax: CREATE VIEW vContactAll AS SELECT t1.*, t2.*, t3.* + FROM Contact t1 + LEFT OUTER JOIN client t2 ON t1.id = t2.id + LEFT OUTER JOIN people t3 ON t1.id = t3.id Karen
