Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 31st, 2005, 03:20 PM
Registered User
 
Join Date: Oct 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Combining Queries or results from 2 queries

I was wondering is it possible to combine the results
from two different queries?

My example is simpler than reality but I wanted to get my ideas across.

Within a single SPROC:
My first query grabs a date range(let's say, 01/05 - 10/05)
and it grabs all books sold in the fiction department

My second query grabs a date range(let's say, 01/05 - 10/05)
and it grabs all books sold in the non-fiction department.

I want to combine the columns from the different queries and have
them link up based upon the common factor... this being the date range.

There are possibilities that no books were sold during a particular month, and thus would not pull back a record for that month, making the number of rows different from query 1 and query 2.

Any and all ideas are appreciated.

Thanks.
Reply With Quote
  #2 (permalink)  
Old October 31st, 2005, 03:26 PM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

select * from table1 where ..
union all
select * from table2 where ..


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
Reply With Quote
  #3 (permalink)  
Old October 31st, 2005, 06:41 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

your can't do order by on union commands, so another option is to load all the data into a temp table, then select from the temp table

Reply With Quote
  #4 (permalink)  
Old October 31st, 2005, 09:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by robprell
 you can't do order by on union commands ...
Sure you can.

The ORDER BY simply applies to the combined results of all the queries comprising the UNION operator. The ORDER BY cannot be applied to each constituent query, since that would make no sense. But it makes perfect sense to order the combined result.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old November 1st, 2005, 12:19 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hummm perhaps I learn something new, can you give me an example without using sub selects?

Reply With Quote
  #6 (permalink)  
Old November 1st, 2005, 02:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
...without using sub selects...
Not sure what you mean by that comment...

A UNION query is made up of two or more SELECT statements combined with the UNION operator, as:
Code:
    SELECT col1, col2, col3, ...
    FROM <sometable>
    WHERE ...
UNION
    SELECT colA, colB, colC, ...
    FROM <someothertable>
    WHERE ...
ORDER BY col1, ...
The ORDER BY clause applies to the results of the UNION as a whole. The resultset of a UNION has the column names (and datatypes) of the first SELECT statement, so the ORDER BY clause refers to columns in that first query.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #7 (permalink)  
Old November 1st, 2005, 08:06 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks your right I thought it would only order by on the second select, leaving the first in random order. It's the group by and agregations that require a temp table when trying to Union.

Reply With Quote
  #8 (permalink)  
Old November 1st, 2005, 09:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by robprell
It's the group by and agregations that require a temp table when trying to Union.
Well, you still don't need a temp table for that - you can materialize a table anytime you want - the result of a SELECT statement is, after all, a table. Just give it a name and use it anywhere a "real" table would be used. Consider:
Code:
SELECT DerivedTable.Col1, MAX(DerivedTable.col2) FROM
    (SELECT col1, col2, col3, ...
        FROM <sometable>
        WHERE ...
     UNION
        SELECT colA, colB, colC, ...
        FROM <someothertable>
        WHERE ...) as DerivedTable
GROUP BY DerivedTable.Col1
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #9 (permalink)  
Old November 2nd, 2005, 06:14 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

correct but I did say without sub selects.

Reply With Quote
  #10 (permalink)  
Old November 2nd, 2005, 06:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by robprell
 correct but I did say without sub selects.
Yes, and I originally responded to the ORDER BY question by stating that I didn't understand what you meant by the comment. I still don't.

What "sub selects" are you restricting from use?

Are you complaining that I used a derived table (of the UNION query) in order to use an aggregate? You are correct that you can't use an aggregate on the results of a UNION, but you stated that meant you needed a temp table; I merely pointed out an alternative to that.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining two Queries arholly Access 1 January 16th, 2007 06:40 PM
Joining 3 Queries. rupen SQL Language 1 May 11th, 2006 11:35 AM
Combining Parameter Queries Taarnac SQL Language 0 May 4th, 2005 11:13 AM
combine 2 queries collie SQL Server 2000 2 November 29th, 2004 03:09 PM
Queries xzvi0r Access 5 September 8th, 2003 10:03 AM



All times are GMT -4. The time now is 05:05 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.