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,Initiated,+
HighSchoolClassOf,HighSchoolName,CurHomeAudAction,CurHomeAudDate,+
CurHomeAudTime,CurWorkAudAction,CurWorkAudDate,CurWorkAudTime,DateOfDeath,+
AcademicRecCnt,MbrStatus,RegisterChgDate,MailStatus,MAdrSource,FaceBookLit,+
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,HighSchoolName,+
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,WAdrSource,+
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
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
