Combining Queries or results from 2 queries

Ford · October 31st, 2005, 03:20 PM

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.

SqlMenace · October 31st, 2005, 03:26 PM

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/

robprell · October 31st, 2005, 06:41 PM

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

Jeff Mason · October 31st, 2005, 09:20 PM

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

robprell · November 1st, 2005, 12:19 PM

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

Jeff Mason · November 1st, 2005, 02:24 PM

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

robprell · November 1st, 2005, 08:06 PM

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.

Jeff Mason · November 1st, 2005, 09:20 PM

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

robprell · November 2nd, 2005, 06:14 PM

correct but I did say without sub selects.

Jeff Mason · November 2nd, 2005, 06:38 PM

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