'joining' results of 2 queries

[Matt O'Donnell]

Does anyone know how I can 'join' the results of
one SQL query to the bottom of another?

Eg. I have two queries:
1. SELECT Name, Surname FROM People WHERE Surname = Smith
NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
2. SELECT Name, Surname FROM People WHERE Surname = Jones
NAME SURNAME

Bob Jones
Larry Jones
Tom Jones

What I want to produce is:
NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
Bob Jones
Larry Jones
Tom Jones
However, if I use UNION like this:

SELECT Name, Surname FROM People WHERE Surname = Smith
UNION
SELECT Name, Surname FROM People WHERE Surname = Jones
it mixes up all the results:

NAME SURNAME

Adam Smith
Bob Jones
John Smith
Larry Jones
Michael Smith
Steve Smith
Tom Jones
(I guess it's sorting by the first field, NAME).

Is there a way to stop it sorting the results, so that it
just tacks the second query results to the bottom of the
first query results?
(I realise I could use "ORDER BY Surname" to get the same result
in this simple example, but for the more complicated queries
I want to use it won't work).

Thanks for any help,

Matt.

[louis nguyen]

Hi Matt,

Union sorts the data and tries to remove duplicates. Use UNION ALL to
override this behavior. If the data is stored in a table, use an
identity column to preserve the order. - Louis

[duncan]

od**************@hotmail.com (Matt O'Donnell) wrote in message news:<cc**************************@posting.google. com>...

Does anyone know how I can 'join' the results of
one SQL query to the bottom of another?

Eg. I have two queries:
1. SELECT Name, Surname FROM People WHERE Surname = Smith
NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
2. SELECT Name, Surname FROM People WHERE Surname = Jones
NAME SURNAME

Bob Jones
Larry Jones
Tom Jones

What I want to produce is:
NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
Bob Jones
Larry Jones
Tom Jones
However, if I use UNION like this:

SELECT Name, Surname FROM People WHERE Surname = Smith
UNION
SELECT Name, Surname FROM People WHERE Surname = Jones
it mixes up all the results:

NAME SURNAME

Adam Smith
Bob Jones
John Smith
Larry Jones
Michael Smith
Steve Smith
Tom Jones
(I guess it's sorting by the first field, NAME).

Is there a way to stop it sorting the results, so that it
just tacks the second query results to the bottom of the
first query results?
(I realise I could use "ORDER BY Surname" to get the same result
in this simple example, but for the more complicated queries
I want to use it won't work).

Thanks for any help,

Matt.

WHy not use

SELECT Name, Surname FROM People WHERE Surname = 'Jones' or 'SMITH'
ORDER BY Surname, Name
Simple and avoids the need for a UNION

HOpe this helps
Duncan

[Carl Johansen]

I agree that in this case you should just use an OR, but assuming your real
case is more complicated, how about

SELECT 1 AS ResultSetNum, Name, Surname FROM People WHERE Surname = Smith
UNION ALL
SELECT 2 AS ResultSetNum, Name, Surname FROM People WHERE Surname = Jones
ORDER BY ResultSetNum

"duncan" <dw***@woodace.co.uk> wrote in message
news:8f**************************@posting.google.c om...

od**************@hotmail.com (Matt O'Donnell) wrote in message

news:<cc**************************@posting.google. com>...

Does anyone know how I can 'join' the results of
one SQL query to the bottom of another?

Eg. I have two queries:
1. SELECT Name, Surname FROM People WHERE Surname = Smith
NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
2. SELECT Name, Surname FROM People WHERE Surname = Jones
NAME SURNAME

Bob Jones
Larry Jones
Tom Jones

What I want to produce is:
NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
Bob Jones
Larry Jones
Tom Jones
However, if I use UNION like this:

SELECT Name, Surname FROM People WHERE Surname = Smith
UNION
SELECT Name, Surname FROM People WHERE Surname = Jones
it mixes up all the results:

NAME SURNAME

Adam Smith
Bob Jones
John Smith
Larry Jones
Michael Smith
Steve Smith
Tom Jones
(I guess it's sorting by the first field, NAME).

Is there a way to stop it sorting the results, so that it
just tacks the second query results to the bottom of the
first query results?
(I realise I could use "ORDER BY Surname" to get the same result
in this simple example, but for the more complicated queries
I want to use it won't work).

Thanks for any help,

Matt.

WHy not use

SELECT Name, Surname FROM People WHERE Surname = 'Jones' or 'SMITH'
ORDER BY Surname, Name
Simple and avoids the need for a UNION

HOpe this helps
Duncan