Karen,
Not quite sure how to select a specific common column.
Along time ago I went through my tables and modified them
so that only linking columns remained. I had used a "LastChgDate"
in many tables. My table structure:
MbrRegister -- master table linking col MbrNumber
MbrCurHomeInfo
MbrCurWorkInfo
MbrCurEmailInfo
... additional subordinate table
When I created multi-table forms with had common "LastChgDate"
columns the form would not work. Went through and renamed those
columns to make them unique. This eliminated that problem.
When you write the FROM clause remember that RBASE requires
the use of "()" to delimit each join. The FROM clasuse is evaluated
strictly LEFT to RIGHT and ignores the "()"
As to the renaming part try SELECT Col1, col2, Col3 as Karen, Col4 ...
note since you are using CREATE VIEW viewname (col1,col2,col3,col4...) AS Select
You could just do CREATE VIEW viewname (col1, col2, Karen, col4 ...) AS Select
Note!! When i develop the FROM clauses I try to use the concept of most
restricted to least restricted
in creating the left to right order of JOINS. Thus the LEFT most join wold be
the most restricted (returns smallest number
of values). You can nest INNER and OUTER JOINS.
Jim Bentley,
American Celiac Society
1-504-737-3293
--------------------------------------------
On Wed, 10/30/13, Karen Tellef <[email protected]> wrote:
Subject: [RBASE-L] - Re: Left Outer Join
To: "RBASE-L Mailing List" <[email protected]>
Date: Wednesday, October 30, 2013, 10:39 AM
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
