I have a table A that has child records in Table B or Table C. Table B and C are basically the same structure and mutually exclusive (I'm merging two old systems.)

So, when I wrote the SQL for a summary view in the View builder it created this:

SELECT A.field1, ...;
FROM A;
LEFT OUTER JOIN B;
LEFT OUTER JOIN C;
ON A.field1=B.field1;
ON A.field1=C.field1;
GROUP BY A.field1

But this either takes two days or simply doesn't work on tables of only 20-100 thousand records each. I didn't wait to find out.

So, playing with the SQL code I changed it to:

SELECT field1, ...;
FROM A;
LEFT OUTER JOIN B;
ON A.field1=B.field1;
LEFT OUTER JOIN C;
ON A.field1=C.field1;
GROUP BY A.field1

This was quick (enough) and appears to work just fine. But I need a view, not a Query. So how do I get this back into by View SQL?

Any comments regarding why my SQL is wrong or how to get my view to accept it would be appreciated.

Thanks in advance.