HOLY FRIJOLES! This is the single biggest improvement in the R:Base engine since I can remember, and I haven't seen it mentioned ANYWHERE (except your post). I just checked WhatsNewInRBase76ForWindows.PDF, and couldn't find it in there. I had no idea that R:Base supported SQL-92 JOIN syntax!
-- Larry ________________________________ From: James Bentley <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, November 17, 2010 7:43:43 PM Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables? Bruce, You are correct I had problems in cutting the data from working code. Was hurrying to go to class and took a shortcut. Here is the corrected info: FROM (((((RegisterOfMbrs R LEFT OUTER JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber) J1 LEFT OUTER JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 LEFT OUTER JOIN AcademicAsList A ON J2.MbrNumber=A.MbrNumber) J3 LEFT OUTER JOIN DirHighSchools S ON J3.HighSchoolNbr=S.HighSchoolNbr) J4 LEFT OUTER JOIN MbrFromChapter F ON J4.MbrNumber=F.MbrNumber) J5 LEFT OUTER JOIN MbrFathersInfo D ON J5.MbrNumber = D.MbrNumber Hope you were able to correctly figure out. The key is to match the parenthesis. Also you can mix the nesting of join types. In my case it is LEFT OUTER JOIN. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ----- Original Message ---- > From: Bruce Chitiea <[email protected]> > To: RBASE-L Mailing List <[email protected]> > Sent: Wed, November 17, 2010 2:49:20 PM > Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables? > > Larry, Bill, Jim: > > Thanks much: powerful stuff. > > Jim: Here's the example, abstracted: > > FROM + > ( ( ( ( ( + > tableview1 t1 + > > INNER JOIN + > tableview2 t2 + > ON t1.keycolumn=t2.keycolumn + > ) J1 + > > INNER JOIN + > tableview3 t3 + > ON J1.keycolumn=t3.keycolumn + > ) J2 + > > INNER JOIN + > tableview4 t4 + > ON J2.keycolumn=t4.keycolumn + > ) J3 + > > INNER JOIN + > tableview5 t5 + > ON J3.keycolumn=t5.keycolumn > > I'm assuming that this was cut from working code, skewing the number of > open/close parens. If this were stand-alone, would I open with: > > FROM + > ( ( ( ( + > > and close with: > > ON J3.keycolumn=t5.keycolumn + > ) J4 > > > Yours, > > bruce chitiea > safesectors inc > > > -------- Original Message -------- > > Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables? > > From: James Bentley <[email protected]> > > Date: Wed, November 17, 2010 11:50 am > > To: [email protected] (RBASE-L Mailing List) > > > > > > Better still RBase supports nested INNER and OUTER JOINS. Try following >syntax; > > > > FROM (((((RegisterOfMbrs R INNER + > > JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber) J1 INNER + > > JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 INNER + > > JOIN AcademicAsList A ON J2.MbrNumber=A.MbrNumber) J3 INNER + > > JOIN DirHighSchools S ON J3.HighSchoolNbr=S.HighSchoolNbr > > > > You can also nest a mixture of INNER JOINS, LEFT OUTER JOIN, RIGHT OUTER >JOIN, > > > and FULL OUTER JOIN. The key is proper use of Parentheses. This syntax > > available in v7.6 (Windows & DOS) and higher. > > > > > > Jim Bentley > > American Celiac Society > > [email protected] > > tel: 1-504-737-3293 > > > > > > > > > >From: Lawrence Lustig <[email protected]> > > >To: RBASE-L Mailing List <[email protected]> > > >Sent: Tue, November 16, 2010 1:21:50 PM > > >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables? > > > > > > > > >You can perform multiple INNER JOINs in a single statement using the > > >older > > >SQL-89 syntax: > > > > > > > > >SELECT * FROM table1, table2, table3 + > > > WHERE table1.colX = table2.colY AND table2.colA = table3.colB > > > > > > > > >Unfortunately, this works only for implicit INNER JOINs in the older >syntax. > > > > For SQL-92 syntax (which is the only way to do OUTER JOINs) you are >limited to > > > >2 tables per SELECT statement. The work-around there is to use nested >views as > > > >described in your message. > > >-- > > >Larry > > >
