Syntax for a Union where tables are slightly different

[awise]

I'm trying to union join (2) tables that have the same columns, however, they're in unlike order.

A work table and it's associated history table. I want to create a new table (View) that will hold all the records from both tables. In attempting, I've discovered that the tables have all the same columns an associated field types,
however, as I mentioned, the columns are not in the same order (Also a few like columns have differring naming convention as well). Very unusual.

Is there a way to, either, union these tables in total, or is there a way to specify just the columns I want and get the records from both tables to populate the new view?

Appreciate any ideas and / or assistance.

Thank you,

zaw

 

[monksnake]

Just reorder your select clause so they both pull the same fields in the same place. (You can specify the columns you want and in the "order" you want them)



<.

 

[gmmastros]

If you try...

Select * From Table1
Union All
Select * From Table2

This won't work. However....

Select Col1, Col2, Col3 From Table1
Union All
Select ColA, Col2, ColX From Table2

The trick here is... each query involved in the union MUST return the same number of columns and each column (in the order you specify) must return the same data type.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You just need to specify columns in the same order. Say you have these tables:

tbl1

Col1
Col3
Col2

tbl2

Col1
Col2
Col3

And you want them unioned properly (in the order they appear in tbl1).

Your query would just be

[COLOR=blue]select[/color] Col1, Col3, Col2
[COLOR=blue]from[/color] tbl1
union [COLOR=blue]select[/color] Col1, Col3, Col2
[COLOR=blue]from[/color] tbl2

(Assuming that all columns have the same data types between tables, because you can only combine columns with similar data types in a UNION query)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Of course. But you should use field list in your query:

[COLOR=blue]SELECT[/color] Fled1,...,FieldN [COLOR=blue]FROM[/color] MainTable
UNION ALL
[COLOR=blue]SELECT[/color] Fled1,...,FieldN [COLOR=blue]FROM[/color] HistoryTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP