471,078 Members | 816 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,078 software developers and data experts.

'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.
Jul 20 '05 #1
3 12757
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
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
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.

Similar topics

6 posts views Thread by paulus4605 | last post: by
1 post views Thread by Ondernemer | last post: by
1 post views Thread by Beachvolleyballer | last post: by
reply views Thread by Norma | last post: by
2 posts views Thread by Alec | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.