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
