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/> To: RBASE-L Mailing List <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
