I never thought-about/looked-at it the way that Bruce laid it out. I like that presentation.
Example-wise, for Bill M, I have the following. 3 tables: Ø COMPETITION_RESULTS (Individual Race Performances, with FK/linking values for TEAMS and COMPETITIONS) Ø TEAMS (School/Club/Team, team colors, etc. ) Ø COMPETITIONS (League/Invitational/Championship meets) SELECT CompetitionID AS CompID, + NameOfSchoolCD AS SCHL, + CompetitionName, + NameOfSchool, + TeamForeGroundColor AS FRGRND, + TeamBackGroundColor AS BKGRND + FROM ( ( INDIVIDUAL_RESULTS ir LEFT OUTER JOIN COMPETITIONS comp ON comp.CompetitionID = ir.CompetitionID ) resultA LEFT OUTER JOIN SCHOOLS schl ON schl.NameOfSchoolCD = resultA.NameOfSchoolCD ) resultB WHERE (IYR4(PerformanceDT)) = 2014 Some output from the result-set (just the stuff correlated by the join's, not individual names, times, place, etc): Comp'ID SCHL CompetitionName NameOfSchool FRGRND BKGRND -------- ------ ------------------------------ ------------------------- --------------- --------------- 00000123 MUS Shelby78 Weekly League Meet 04 Memphis University School [R255,G83,B95] [R15,G0,B255] 00000123 MUS Shelby78 Weekly League Meet 04 Memphis University School [R255,G83,B95] [R15,G0,B255] 00000123 SAA Shelby78 Weekly League Meet 04 St. Agnes Academy [R255,G255,B255] [R0,G104,B208] 00000123 SAA Shelby78 Weekly League Meet 04 St. Agnes Academy [R255,G255,B255] [R0,G104,B208] 00000123 SDS Shelby78 Weekly League Meet 04 St. Dominic School [R255,G224,B48] [R71,G0,B191] 00000123 SDS Shelby78 Weekly League Meet 04 St. Dominic School [R255,G224,B48] [R71,G0,B191] 00000123 SGIS Shelby78 Weekly League Meet 04 St. George's [R155,G33,B33] [R248,G211,B118] 00000123 SGIS Shelby78 Weekly League Meet 04 St. George's [R155,G33,B33] [R248,G211,B118] I am pretty sure that some dialects of SQL allow this JOIN-syntax, without the paren's, but I guess that can vary by implementation: SELECT ir.CompetitionID AS CompID, + ir.NameOfSchoolCD AS SCHL, + comp.CompetitionName, + schl.NameOfSchool, + schl.TeamForeGroundColor AS FRGRND, + schl.TeamBackGroundColor AS BKGRND + FROM INDIVIDUAL_RESULTS ir LEFT OUTER JOIN COMPETITIONS comp ON comp.CompetitionID = ir.CompetitionID LEFT OUTER JOIN SCHOOLS schl ON schl.NameOfSchoolCD = resultA.NameOfSchoolCD WHERE (IYR4(ir.PerformanceDT)) = 2014 Anyway, hope this helps, too. Steve in Memphis From: [email protected] [mailto:[email protected]] On Behalf Of BILL MILYARD Sent: December 12, 2014 10:19 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: FW: [RBASE-L] - Multiple Joins Thank you all. Bruce's example works. I'm not familiar with that syntax but it is quite clear. ________________________________ From: [email protected]<mailto:[email protected]> To: [email protected]<mailto:[email protected]> Subject: [RBASE-L] - FW: [RBASE-L] - Multiple Joins Date: Fri, 12 Dec 2014 08:01:28 -0800 Bill: A re-post. Again, thanks to Paul Buckley for being JoinMaster. My 'toast' comment below is a tad flip, but true. What makes JOINs work for me is maintaining a highly ordered visual structure, and treating the table and join aliases as members of a matrix: t1 t2 - t2 - t1 - j1 t3 - t3 - j1 - j2 t4 - t4 - j2 - j3 ... tn - tn - j(n-2) - j(n-1) Hope this helps Bruce From: Bruce A. Chitiea [mailto:[email protected]] Sent: Monday, December 08, 2014 4:59 PM To: '[email protected]' Subject: RE: [RBASE-L] - Multiple Joins Bill: Paul Buckley helped me with much the same question several years ago. FWIW here's a setup I use to create an R:Charts annual/monthly water consumption model. Maybe there's clue here. If the "SELECT ALL" statement is reformatted in the email, each line terminates with "LEFT OUTER +". Lose your parentheses count and you're toast. Hope this helps. Bruce Chitiea SafeSectors, Inc. eCondoMetrics CREATE TEMP VIEW Chart_A420_JoinUp_tvw + ( WYear, + WY01JAN, YM01JAN, WU01JAN, WC01JAN, + WY02FEB, YM02FEB, WU02FEB, WC02FEB, + WY03MAR, YM03MAR, WU03MAR, WC03MAR, + WY04APR, YM04APR, WU04APR, WC04APR, + WY05MAY, YM05MAY, WU05MAY, WC05MAY, + WY06JUN, YM06JUN, WU06JUN, WC06JUN, + WY07JUL, YM07JUL, WU07JUL, WC07JUL, + WY08AUG, YM08AUG, WU08AUG, WC08AUG, + WY09SEP, YM09SEP, WU09SEP, WC09SEP, + WY10OCT, YM10OCT, WU10OCT, WC10OCT, + WY11NOV, YM11NOV, WU11NOV, WC11NOV, + WY12DEC, YM12DEC, WU12DEC, WC12DEC ) AS + SELECT ALL + FROM ((((((((((( WYear_tvw t1 LEFT OUTER + JOIN Month01_tvw t2 ON t2.WYear=t1.WYear ) J1 LEFT OUTER + JOIN Month02_tvw t3 ON t3.WYear=J1.WYear ) J2 LEFT OUTER + JOIN Month03_tvw t4 ON t4.WYear=J2.WYear ) J3 LEFT OUTER + JOIN Month04_tvw t5 ON t5.WYear=J3.WYear ) J4 LEFT OUTER + JOIN Month05_tvw t6 ON t6.WYear=J4.WYear ) J5 LEFT OUTER + JOIN Month06_tvw t7 ON t7.WYear=J5.WYear ) J6 LEFT OUTER + JOIN Month07_tvw t8 ON t8.WYear=J6.WYear ) J7 LEFT OUTER + JOIN Month08_tvw t9 ON t9.WYear=J7.WYear ) J8 LEFT OUTER + JOIN Month09_tvw t10 ON t10.WYear=J8.WYear ) J9 LEFT OUTER + JOIN Month10_tvw t11 ON t11.WYear=J9.WYear ) J10 LEFT OUTER + JOIN Month11_tvw t12 ON t12.WYear=J10.WYear ) J11 LEFT OUTER + JOIN Month12_tvw t13 ON t13.WYear=J11.WYear From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of BILL MILYARD Sent: Monday, December 08, 2014 12:12 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Multiple Joins [https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif] [RBASE-L] - Multiple Joins William Milyard 12/01/14 [https://snt146.mail.live.com/ol/clear.gif]<https://snt146.mail.live.com/ol/> To: RBASE-L Mailing List [https://a.gfx.ms/ic/bluemanmxl.png]<https://snt146.mail.live.com/ol/> I've been unable to get multiple joins to work. Does RBase allow multiple joins as: SELECT t1.col1,t2.col1,t3.col3 FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.col1 = t2.col1 LEFT OUTER JOIN t3 ON t1.col1 = t3.col1 This works in other environments but I get a syntax error when I use this in RBase. Is it simply a syntax error or are multiple joins not supported in RBase? Thanks, Bill
![https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif](https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif][https://snt146.mail.live.com/ol/clear.gif){kind=link}
