Larry, According to my records nested inner joins were introduced as early as Feb 2008 as a sample RMD. Some time later the ability to nest all join types were added by never formally introduced or documented.
I agree it was a great addition as it allowed my to simplify many of my views as you can see from my example I have a master table "RegisterofMbrs" with a slew of sub-tables most of which may not have record(s) for a given member. Hence the need for LEFT OUTER JOIN. Formally this required have individual views to create each OUTER JOIN and then a final view to mesh all of the outer join views. Now I have just the one view. 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: Wed, November 17, 2010 9:58:09 PM >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables? > > >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 >> >> >> > > > > > >
