By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,132 Members | 1,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,132 IT Pros & Developers. It's quick & easy.

'joining' results of 2 queries

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
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

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.