James B. You might find the following article helpful http://en.wikipedia.org/wiki/Join_%28SQL%29
Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 >________________________________ > From: Jim Belisle <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Friday, March 16, 2012 10:42 AM >Subject: [RBASE-L] - Re: Exploding BOM > >JAMES, > > > >I have read some on the OUTER JOINS and INNER JOINS but still do not >understand how to do them. > >I will add that to the list of code I need to study. > > > >James Belisle > > > >Making Information Systems People Friendly Since 1990 > > > > > >________________________________ > >From: [email protected] [mailto:[email protected]] On Behalf Of James >Bentley >Sent: Friday, March 16, 2012 10:37 AM >To: RBASE-L Mailing List >Subject: [RBASE-L] - Re: Exploding BOM > > > >Here is the definition of a view illustrating nested joins from my >production address database > >SET ERROR MESSAGES 677 OFF >DROP VIEW RptVuMbrDtlDp >SET ERROR MESSAGES 677 ON >-- >CREATE VIEW RptVuMbrDtlDp >(FChrLastName,LastNameSortSeq,FirstName,MiddleName,+ >LastName,FullNameLFM,MbrNumber,ChpNbrPfxWLtr,OrgChapLit,OrgChapLtrNbr,+ >Salutation,PrsnTitle,DearBroGreet,NickName,SigOtherName,Birthday,Initiat >ed,+ >HighSchoolClassOf,HighSchoolName,CurHomeAudAction,CurHomeAudDate,+ >CurHomeAudTime,CurWorkAudAction,CurWorkAudDate,CurWorkAudTime,DateOfDeat >h,+ >AcademicRecCnt,MbrStatus,RegisterChgDate,MailStatus,MAdrSource,FaceBookL >it,+ >LinkedInLit,HAdrSource,HPhoneNbr,HCellNbr,HExtraAdrLine,HMainAdrLine,+ >HCityStateZip,HCountry,HomeChgDate,WAdrSource,WPhoneNbr,WFaxNbr,JobTitle >,+ >CpyName,WExtraAdrLine,WMainAdrLine,WCityStateZip,WCountry,WorkChgDate,+ >MbrCurEMailAdr,MbrAltEMailAdr,DegreeList,FathersNameLFM,EstAge) + >AS SELECT FChrLastName,LastNameSortSeq,FirstName,MiddleName,LastName,+ >FullNameLFM,MbrNumber,ChpNbrPfxWLtr,(IFLT(MbrNumber,0,'Org >Chapter:',NULL)),+ >(IFLT(MbrNumber,0,ChpNbrWLtr,NULL)),Salutation,PrsnTitle,DearBroGreet,+ >NickName,SigOtherName,Birthday,Initiated,HighSchoolClassOf,HighSchoolNam >e,+ >CurHomeAudAction,CurHomeAudDate,CurHomeAudTime,CurWorkAudAction,+ >CurWorkAudDate,CurWorkAudTime,DateOfDeath,AcademicRecCnt,MbrStatus,+ >RegisterChgDate,MailStatus,MAdrSource,(SSUB('NO,Yes',1+FaceBookRecCnt)), >+ >(SSUB('NO,Yes',1+LinkedInRecCnt)),HAdrSource,HPhoneNbr,HCellNbr,+ >HExtraAdrLine,HMainAdrLine,(IFEXISTS(HCity,HCity+CHAR(32),NULL)+ + >IFNULL(HStateUSPSCode,NULL,IFEQ(HStateUSPSCode,(CHAR(90)+ + >CHAR(70)),NULL,HStateUSPSCode+ CHAR(32)))+ + >IFEXISTS(HUSPSZipCode,HUSPSZipCode,NULL)),HCountry,HomeChgDate,WAdrSourc >e,+ >WPhoneNbr,WFaxNbr,JobTitle,CpyName,WExtraAdrLine,WMainAdrLine,+ >(IFEXISTS(WCity,WCity+CHAR(32),NULL)+ IFNULL(WStateUSPSCode,NULL,+ >IFEQ(WStateUSPSCode,(CHAR(90)+ CHAR(70)),NULL,WStateUSPSCode+CHAR(32)))+ >+ >IFEXISTS(WUSPSZipCode,WUSPSZipCode,NULL)),WCountry,WorkChgDate,+ >MbrCurEMailAdr,MbrAltEMailAdr,DegreeList,D.DadFullNameLFM,+ >(FORMAT(IFNULL(DeathYear,INT((.#DATE - IFEXISTS(Birthday,Birthday,+ >IFEXISTS(Initiated,ADDYR(Initiated,-19),.#DATE)))/365.25),DeathYear - + >IYR4(Birthday)),AgeFmtMask)) 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 MbrCurEmailInfo E ON J2.MbrNumber=E.MbrNumber) J3 LEFT OUTER + >JOIN MbrAltEmailInfo A ON J3.MbrNumber=A.MbrNumber) J4 LEFT OUTER + >JOIN AcademicAsList B ON J4.MbrNumber=B.MbrNumber) J5 INNER + >JOIN DirHighSchools S ON J5.HighSchoolNbr=S.HighSchoolNbr) J6 LEFT OUTER >+ >JOIN MbrFathersInfo D ON J6.MbrNumber = D.MbrNumber > >-- >COMMENT ON VIEW RptVuMbrDtlDp IS 'RptVu Member details (duplex print >version)' >RETURN > > > > > >Jim Bentley >American Celiac Society >[email protected] >tel: 1-504-737-3293 > >________________________________ > >From: Victor Timmons <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Thursday, March 15, 2012 11:38 PM >Subject: [RBASE-L] - Re: Exploding BOM > > > > > >I would like to see this in practice. > >On Thu, Mar 15, 2012 at 2:27 PM, James Bentley < >[email protected]> wrote: > >You can also have multiple levels of nested INNER JOINS and OUTER JOINS. > >See Latest documentation under JOINS topic. > > >Jim Bentley >American Celiac Society >[email protected] >tel: 1-504-737-3293 > >________________________________ > >From: Dan Goldberg <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Thursday, March 15, 2012 1:12 PM > > >Subject: [RBASE-L] - Re: Exploding BOM > > >You are correct. I am thinking of the Celko's nested sets is what is not > >possible. > >Dan > > > >-----Original Message----- >From: Albert Berry >Sent: Thursday, March 15, 2012 10:48 AM >To: RBASE-L Mailing List >Subject: [RBASE-L] - Re: Exploding BOM > >That's not quite true Dan, you can join one table to another. Select >ParentID from Contacts t1, left outer join ChildID from Contacts t2 >where t1.ParentID = t2.ParentID. > >Cannot try it right now - my system just ate my R:Base but I have done >this sort of thing in the past. >Albert > >On 15/03/2012 11:33 AM, Dan Goldberg wrote: >> The problem is rbase cannot join tables to itself and does not recurse >> itself.. That is why you have to use nested cursors. > >> Dan Goldberg >> *From:* Ben Petersen <mailto:[email protected]> >> *Sent:* Thursday, March 15, 2012 10:18 AM >> *To:* RBASE-L Mailing List <mailto:[email protected]> >> *Subject:* [RBASE-L] - Re: Exploding BOM >> I looked a little further, this link is much better with examples and >> code. >> >> http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ >> >> >> >> On Thu, Mar 15, 2012 at 9:36 AM, Ben Petersen <[email protected] >> <mailto:[email protected]>> wrote: >> >> Karen, >> >> Take a look a "nested sets". >> >> http://en.wikipedia.org/wiki/Nested_set_model >> http://www.sqlteam.com/article/more-trees-hierarchies-in-sql >> >> It takes a minute or two to wrap your head around, but is an >> elegant solution (I think) to what you're proposing -- with >> unlimited levels. No cursors, but could be write intensive if that >> is a concern, though I wouldn't imagine any more than a cursor >loop. >> >> Ben >> >> >> >> >> >> On Thu, Mar 15, 2012 at 8:55 AM, <[email protected] >> <mailto:[email protected]>> wrote: >> >> Dan: That's the way I was going to go, a definite number of >> cursors. I feel >> better knowing that at least one person did it in that >> manner! I'll give it a while >> and if no one has code for unlimited levels, then I'll just go >> with that. Thanks! >> >> Karen >> >> >> In a message dated 3/15/2012 10:44:55 AM Central Daylight >> Time, [email protected] <mailto:[email protected]> >writes: >>> >>> I use cursors. I just set up for expected level of boms that >>> we could possibly imagine using. We only go to a max of 6 >>> levels down into the bom so I put in 8 cursor levels to just >>> in case and put an error message if it finds something below >>> 8 levels so I can add more someday. >>> >>> I know Rbase does not support celko's nested boms so I use >>> cursors instead. >>> >>> Dan >>> >>> >>> >> > > > > > > > > > > > >
