After reading and rereading your post I think I have a solution for you.
You are performing two operations on four datasets, TableA, TableB, TableAB(INNER JOIN),TableC.
We start with TableA, TableB, and TableC available to us. We need to create dataset TableAB
by INNER JOINing tables TableA and TableB as our first operation.
-
SELECT A.[column1],B.[column2] FROM
-
TableA A
-
INNER JOIN
-
TableB B
-
ON
-
A.joinField = B.joinField
-
This produces dataset TableAB. Now we can proceed to our second operation, retrieving all rows
from TableC and appending them to the dataset TableAB.
-
SELECT column1,column2 as [ccolumn12] FROM TableAB
-
UNION ALL
-
SELECT [ccolumn7] as [column1],[ccolumn12] FROM TableC
-
To successfully use the UNION ALL, you will need to make sure the column names in TableC match with the column names in the dataset TableAB. In addition to matching column names you will have to match data types as well. You can accomplish this easily by assigning incongruous
fields an homogenizing alias. In the code above I give [ccolumn7] the alias [column1] which matches the field from the TableAB dataset. The second alias in the code sample assigns column2 from the TableAB dataset the alias [ccolumn12] which matches the field name from the TableC dataset.
Now that we have all the pieces we can put this together in one big script.
-
SELECT A.[Column1],B.[column2] as [ccolumn12] FROM TableA A
-
INNER JOIN TableB B ON A.joinField = B.joinField
-
-
UNION ALL
-
SELECT [ccolumn7] as [column1], [ccolumn12] FROM TableC
-
This script combines rows from INNER JOINed datasets TableA and TableB with any and all rows from dataset
TableC.
Let me know how this works out for you!