joining results of 2 queries

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.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|||odonnell_matthew@.hotmail.com (Matt O'Donnell) wrote in message news:<cc73ca09.0311150527.4f160205@.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|||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" <dwang@.woodace.co.uk> wrote in message
news:8fe85207.0311151415.3ece8226@.posting.google.c om...
> odonnell_matthew@.hotmail.com (Matt O'Donnell) wrote in message
news:<cc73ca09.0311150527.4f160205@.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