How to make a union query from tables with different structures

[dougie]

I'm offering this is as a tip as I've recently found it useful in reducing the number of queries in an application - apologies if it's old hat. I have three tables with nothing in common but a date field. I want to collect a number and date from each table and then do things with those numbers (group by month, divide, multiply, total, cross tab etc.). Suppose table1 has fields Date, Quantity, table2 has fields Date, Price and table3 has fields Date, NumberOfWheels. I can make a union as follows:

SELECT Date, Quantity, 0 AS Price, 0 AS NumberOfWheels
FROM table1
UNION ALL
SELECT Date, 0 AS Quantity, Price, 0 AS NumberOfWheels
FROM table2
UNION ALL
SELECT Date, 0 AS Quantity, 0 AS Price, NumberOfWheels
FROM table3;

If you have a WHERE clause it must be appended to all three FROM statements, like this:

SELECT Date, Quantity, 0 AS Price, 0 AS NumberOfWheels
FROM table1 WHERE Year(Date)=2012
UNION ALL
SELECT Date, 0 AS Quantity, Price, 0 AS NumberOfWheels
FROM table2 WHERE Year(Date)=2012
UNION ALL
SELECT Date, 0 AS Quantity, 0 AS Price, NumberOfWheels
FROM table3 WHERE Year(Date)=2012;

[June7]

Yes, this is known but congratulations for discovering on your own. Note that it is not necessary to repeat the alias field names in each UNION line. This should also work:

SELECT Date, Quantity, 0 AS Price, 0 AS NumberOfWheels FROM table1 WHERE Year(Date)=2012
UNION ALL SELECT Date, 0, Price, 0 FROM table2 WHERE Year(Date)=2012
UNION ALL SELECT Date, 0, 0, NumberOfWheels FROM table3 WHERE Year(Date)=2012;

Could use Null as placeholder instead of 0 or text.

However, I am not sure why a query that joins the 3 tables would not serve.

Also, Date is a reserved word. Should avoid reserved words as field names, otherwise enclose in [].