Using Unions in SQL Server 2000

Question

Using Unions in SQL Server 2000

reddy_prathap

Valued Member

Points: 53
More actions
December 4, 2003 at 7:32 am

#358693

Hi,
I want to perform some union operation between 2 tables. for example from Northwind database i written query as
select CategoryID, CategoryName from Categories
UNION
select EmployeeID,LastName from Employees
When i am using the statement like that it is showing the result which contains records from both table and it was sorted according to ID of Category and Employee.
Now the Question is , is it possible to get the result with out sorting i.e i want to get the list of Categories first and want to get the Employees list second in single result(without getting merged Employees in between categories and vice versa.). I know that this can be achieved through programming getting categories first into one datatable and employees into another datatable and clubing these 2 datatable to single datatable. Is there solution to do the same using SQL Server..
Cheers
Pratap (+91 9849781009)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

quote:

 

select CategoryID, CategoryName from Categories

UNION  ALL  

select EmployeeID,LastName from Employees

* Noel

gljjr SSChampion Points: 10080 More actions · Answer 2

While using the "All" keyword will seem to do what you want a more reliable solution would be something like the following...



select CategoryID, CategoryName, 1 Sort

from Categories

UNION

select EmployeeID,LastName , 2 Sort

from Employees

ORDER BY Sort, CategoryName

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

noeld SSC Guru Points: 96590 More actions · Answer 3

quote:
gljjr While using the "All" keyword will seem to do what you want a more reliable solution would be something like the following...
select CategoryID, CategoryName, 1 Sort
from Categories
UNION
select EmployeeID,LastName , 2 Sort
from Employees
ORDER BY Sort, CategoryName

in this case if there is a category name with the same name as employee last name you get the wrong order

* Noel

adamrogas SSC Journeyman Points: 93 More actions · Answer 4

No you wouldn't, That is why the sort column was added, hardcoded with the 1 for the first query and 2 for the second. The sort field is ordered first thus you get all of the First query, then all of the second query.