I have two SQL SELECT statements. As an example, say they are “SELECT name FROM people” and “SELECT address FROM billing”. I want to return a result set that is all the results from the first SELECT followed by all the results from the second SELECT. Is there an easy way to do this using only SQL? TIA!
–FCOD
SELECT people.name, billing.address from name, billing.
you’ll probably need a where clause at the end though to make sure that you match up the correct records. Is there data that is present in both tables?
This sounds a bit like a union statement situation. Typical syntax is like this:
select name from people
union
select address from billing
This will give you one result set with names and addresses all in the same column, called ‘name’. The order might be hard to predict without a specific order by clause, and as is it might delete duplicate results. (Try ‘union all’ to preserve duplicates.)
Hope this helps.
What is your SQL data source?
Well, I can’t expect helpful answers when I don’t ask the question correctly. The two SELECT statements are the same except for different WHERE clauses. Here are new example statements:
“SELECT * FROM people WHERE type=1”
“SELECT * FROM people WHERE type=2”
Actually, you were helpful. UNION is exactly what I was looking for. Thank you!
–FCOD
select 1, A from B
union
select 2, X from Y
order by 1
If the only difference is different where clauses, then I think it is better to try to form a where clause that will encompass both cases. Either of these would work, I think:
“SELECT * FROM people WHERE type=1 or type = 2”
“SELECT * FROM people WHERE type in (1, 2)”
UNION is meant for situations where the underlying tables are different, mostly. Still, if it works it works. 
If you SQL geeks wouldn’t mind, I have a question over here.
That would work if I didn’t need each query’s results to be sorted differently and then concatenated. Since I don’t want the results to be mixed together but one after the other, I think UNION is the only way to go. I’m pretty good at SQL, but I have never used the UNION statement before.
–FCOD
I don’t think the UNION clause guarantees the order in which rows are returned. Better is
SELECT * FROM people WHERE type in (1,2) ORDER BY type
If you really need them to be sorted according to different rules, then yes, you may need the ‘union’ operator for that. If the lists can be sorted according to the same criteria amongst themselves, but listed seperately, then it’s easy to use the combined where clause and say: order by type, address, phonenumber or whatever.
Notice that the union-select statement also has at most a single order by clause, at the end, which uses column numbers or column names as defined in the first select list. You cannot say something like this:
SELECT name, address, phonenumber, type FROM people WHERE type=1
order by phonenumber
UNION
SELECT name, address, phonenumber, type FROM people WHERE type=2
order by address
The first ‘order by’ clause will generate a syntax error IIRC. If you take it out, the second order by clause will affect the entire result set.
however, you can do tricky stuff like this:
SELECT name, address, phonenumber, type, ‘1|’ + phonenumber sortfield FROM people WHERE type=1
UNION
SELECT name, address, phonenumber, type, ‘2|’ + address sortfield FROM people WHERE type=2
order by sortfield
Which should get the results of: type 1 people sorted by their phone number, then type 2 people sorted by their address. There will be the extra ‘sortfield’ column in your result set, but you shouldn’t need to use that or display it to your user.
Does that make any sense??
Here’s what I did:
I have one table of products. Each product, among other fields, has a color field. There are a lot of distinct values in this field, i.e. “Amber”, “Amber Violet”, “Black”, “Shiny Black” and so on. My queries are as follows:
SELECT * FROM product WHERE color = ‘Amber’;
SELECT * FROM product WHERE color RLIKE ‘Amber’ AND color != ‘Amber’;
As you can see, I want to show all the results from the “exact” search first, followed by the results of the “similar” search. To expand:
(SELECT * FROM product WHERE color = ‘Amber’ ORDER BY name)
UNION
(SELECT * FROM product WHERE color RLIKE ‘Amber’ AND color != ‘Amber’ ORDER BY name)
LIMIT 20;
This is a simplified version of what I am going to use. The actual statements involve joining 5 tables as well, but you get the idea.
Because I want to sort the two result sets individually and then LIMIT them as one, I think UNION is my only option.
chrisk: You remember correctly. However, placing the two SELECT statements within parenthesees will correct the error.
–FCOD
BTW I’m using MySQL. If you use another DB engine YMMV.
–FCOD
I think you can get what you want by saying
SELECT * FROM product WHERE color RLIKE ‘Amber’ ORDER BY color, name
That should put ‘Amber’ ahead of ‘Amber violet’, ‘Amber pink’, etc. (if RLIKE works the way I’m guessing - I’m not familiar with that operator but I assume it’s like Oracles LIKE ‘Amber%’).
Whoops, no, I lied, ignore my previous post - I forgot about the different sorting in the two halves.
FYI - WHERE x RLIKE pattern - Matches x against a regular expression pattern
–FCOD