Jack, At the present time RBase does not support nested OUTER JOINS. It only supports nested INNER JOINS. At present the only way to accomplish what you want is to create the first needed outer join as a view (note for efficiency sake choose the most restrictive join). Then you use that view as the left side of a join with the next table. If you needed the second item in the join to also be an outer join then create a view of that join. You would then used a third view using the two previous joins instead of the table.
Hopefully at some point V9.0 will support nested OUTER JOINs and also nester INNER and OUTER JOINs. Please join me in requesting that enhancement. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: Jack Mathis <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tuesday, September 29, 2009 5:59:33 PM Subject: [RBASE-L] - Re: Making left outer join in a view Thank you. I wanted two left outer joins in this view. I was able to create it using the Query Wizard, but you couldn’t rename the fields, however you could create two outer joins, it just wouldn’t save because of syntax error. I found if I make two different views with one left outer join at a time it worked and to rename the fields I needed to grab the query, modify it, and use the command line. Thanks From:[email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, September 29, 2009 3:22 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Making left outer join in a view I don't think I've ever used a left outer join yet, but from my instructions here's an example. You would just add the "create view viewname (colname list) as" in front of it: SELECT t2.TransId, + MAX(t1.NetAmount), SUM(t2.ExtPrice) + FROM TransMaster t1 LEFT OUTER JOIN + TransDetail t2 ON + t1.TransId = t2.TransId + GROUP BY t2.TransId HAVING + SUM(t2.ExtPrice) <> MAX(t1.NetAmount) Karen How do you make a Left Outer Join in a view for V8?
