Thanks all,
I will try Larry's suggestion. I am not as familar with INNER/OUTER joins but will give it a try. Having two views is not a problem. It meets my criteria of not having to drop and project temp tables will be used in monitoring live data. If I get the views worked out, I will then review the speed on them. Thanks again. -Bob ----- Original Message ----- From: "Lawrence Lustig" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, March 11, 2010 9:03:32 AM GMT -06:00 US/Canada Central 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
