I often wish for this. Dennis McGrath
________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of James Bentley Sent: Thursday, March 11, 2010 10:19 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Union view I aggree with Larry's solution. I do thos all the time. It would be helpful if RBase allowed NESTED: 1. OUTER JOINS 2. OUTER JOINS and INNER JOINS At present it only allows nested INNER JOINS. I am curious of the how many users on this list could use the nesting type listed in 1 and 2 above Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: Lawrence Lustig <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, March 11, 2010 9:03:32 AM Subject: [RBASE-L] - Re: Union view << Now for the tricky part. Tables 1 and 2 will always have matching KeyColumn values, that would return all rows. I need a UNION for table 3 as it will NOT always contain data for each row in tables 1 and 2 but I need to see ALLl rows, whether table 3 has data or not. Can you do a computed column with a union select? >> You can do this in several different ways, and a SELECT UNION is not strictly necessary. What you need is an OUTER JOIN. Roughly, what you want is: SELECT Somecolumns FROM T1 INNER JOIN T2 ON T1.KeyColumn = T2.KeyColumn OUTER JOIN T3 ON T3.KeyColumn = T1.KeyColumn The only problem is that R:Base does not support combining INNER and OUTER JOINs in a single SELECT statement, nor does it support using more than one OUTER JOIN in a single SELECT statement. To get around this you must perform your INNER JOIN in view, and then OUTER JOIN the view to table 3: CREATE VIEW PartialSolution (ColList) AS SELECT SomeColumns FROM T1, T2 WHERE T1.KeyColumn = T2.KeyColumn SELECT SomeColumns FROM PartialSolution LEFT OUTER JOIN T3 ON PartialSolution.KeyColumn = T3.KeyColumn There is a way to work around this using UNION SELECT, but it's a bit more complicated and, I think, less clear. -- Larry
