Union & union all

[jasonbarnes]

What's the correct syntax for these queries with multiple tables? I can't seem to find an example anywhere online.

Somewhere I read to replicate as follows...

TABLE TBLONE UNION ALL SELECT * FROM TBLTWO
UNION ALL SELECT * FROM TBLTHREE;

It said to duplicate the UNION ALL SELECT FROM TBLTHREE and follow with the ;

I would also like to have WHERE include, such as


WHERE FIELD1 is NULL for all the tables. Then I'll duplicate the query WHERE FIELD1 is NOT NULL.

[alansidman]

These two links might help

http://www.techonthenet.com/sql/union.php

http://www.techonthenet.com/sql/union_all.php

Alan

[jasonbarnes]

My problem is doing a union on three or four tables. I don't see this code anywhere.

[boblarson]

My problem is doing a union on three or four tables. I don't see this code anywhere.

It would simply be something like:

Select * FROM Table1 WHERE FieldA='SomethingHere'
UNION ALL
SELECT * FROM Table2 WHERE FieldA='SomethingHere'
UNION ALL
SELECT * FROM Table3 WHERE FieldX='SomethingElseHere'

Now you can use the * for each of them PROVIDED that you have the exact same number of fields in each of the tables and the fields are in the same order if you want the same data together (in Access it would put all of the data together in the same column even if it is different types, but it would use a string for that and it wouldn't be what you would likely want. In SQL Server this would throw an error because they have to not only have the same field count, but it also needs to have the same datatypes for those same columns). Otherwise you would have to use something like this to account for differences in column count.

Select FIELD1, FIELD2, FIELD3, FIELDA FROM Table1 WHERE Field3='SomethingHere'
UNION ALL
SELECT MyField, ThatField, AnotherField, Null  FROM Table2 WHERE AnotherField='SomethingHere'
UNION ALL
SELECT Null, ThatField, FIELD3, FIELDA FROM Table3 WHERE FieldA='SomethingElseHere'

And you do NOT need the semi-colon at the end for Access.

[jasonbarnes]

I think part of the problem was the semi-colon. The big one was my larger fields weren't large enough in some of the tables to trigger the memo field. All of this data is imported and I generally use the types access creates. Once I made sure all the types/columns matched it worked fine.

Thanks for the help!!