Needing Help Creating a Union Query

[TJVFree]

I need to take four queries and make them into one table

This is what I get when I just do a simple select query.

 SELECT 
Wholesale_Rank_qry_2.OMNI_Number, 
Wholesale_Rank_qry_2.branch, 
Wholesale_Rank_qry_2.branch_name, 
Wholesale_Rank_qry_2.[Rank>], 
Wholesale_Rank_qry_2.[0to5Rank], 
Wholesale_Rank_qry_2.Star_Rating, 

Table_Rank_qry_2.[Rank>], 
Purchase_Rank_qry_2.[Rank>], 
CUSB_Rank_qry_2.[Rank>]

FROM CUSB_Rank_qry_2 RIGHT JOIN (Purchase_Rank_qry_2 RIGHT JOIN (Table_Rank_qry_2 RIGHT JOIN Wholesale_Rank_qry_2 ON Table_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number) ON Purchase_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number) ON CUSB_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number;

I think this is what I want, but I’m receiving a error

The number of columns in the two selected tables or queries of a union query do not match[/]

SELECT [OMNI_Number],[branch],[branch_name],[Rank>],[0to5Rank],[Star_Rating]
FROM [Wholesale_Rank_qry_2]

UNION ALL SELECT [Rank>]
FROM [Table_Rank_qry_2];

UNION ALL SELECT [Rank>]
FROM [Purchase_Rank_qry_2];

This is my first time trying to put together a union query, and I have received a lot of help and training from this form, so I’m hoping someone will be about to help me with learn how to compile a union query.

Thanks for all your help and time

TCB

 

[gmmastros]

There are some fundamental rules concerning UNION (and UNION ALL).

1. Each query MUST return the same number of columns.
2. The order in which you list the columns is extremely important.
3. You cannot put an order by in any of the queries except the last one. The order by applies to the "unioned" result set, not an individual query.

*4. You should make sure that each query's columns return the same data type. You can cast and/or convert the data types, but you should explicitly do this without relying on SQL Server to do this for you because it will sometimes convert things in a way you don't want. For example, an int in one query unioned with a varchar in another query will result in an int output, and as we know, not all strings can be converted to int, so you could get unexpected errors.

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should have mentioned that you can hard code data for the missing columns, like this:

SELECT [OMNI_Number],[branch],[branch_name],[Rank>],[0to5Rank],[Star_Rating]
FROM [Wholesale_Rank_qry_2]

UNION ALL SELECT NULL, NULL, NULL, [Rank>], NULL, NULL
FROM [Table_Rank_qry_2];

UNION ALL SELECT NULL, NULL, NULL, [Rank>], NULL, NULL
FROM [Purchase_Rank_qry_2];

Using NULL for your hard coded "value" will always work regardless of data types.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

3. You cannot put an order by in any of the queries except the last one. The order by applies to the "unioned" result set, not an individual query.

If you must have is sorted by individual queries

you can do it as a sub select

Select field1,field2,field3 
from table1
Union All
Select *
(Select field1,field2,field3 
from table1
Order by field1
)Dt