this is a join - not a union. A union query matches 2 different tables or recordsets with the same "type" in each
matching column.
select col1, col2, col3 from table1
union
select a1, a2, a3 from table2
-- If col1 is an int and a1 is a char, this will fail. They can be named differently but they must have the same type.
This is useful sometimes from the same table when you don't have a 'group" to work with - like grouping by a date range:
-- 30 days ago to present
SELECT dateEntered, title, '30 days' as sortOption
FROM Books
WHERE dateEntered > dateadd(day,-30,getdate())
UNION
SELECT dateEntered, title, '60 days' as sortOption
FROM Books
WHERE dateEntered BETWEEN dateadd(day,-60,getdate())
AND dateadd(day,-30,getdate())
UNION
SELECT dateEntered, title, '60 days' as sortOption
FROM Books
WHERE dateEntered BETWEEN dateadd(day,-90,getdate())
AND dateadd(day,-60,getdate())
ORDER BY sortOption
This would give an ordered result set with groups of books. Of couse, you could overlap or whatever. It's a simple
example - but to do the same thing with "group by" you would need to extract the "month, day and year" because the
"dateentered" will very likely be unique for each record. making a date group option difficult to impliment.
-Mark
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 20, 2004 9:15 AM
To: CF-Talk
Subject: Re: merge data from two queries
can i do a union query with a query like this?
select
count(r.reportId) as reportCount,
v.vehicleName,
c.companyName
from
reports r
left outer join vehicleTable v on v.ipAddressNumber = r.ipAddressNumber
inner join companyTable c on c.companyIdnumber = r.companyId
where
r.companyId = 1
and
r.date between '07/11/2004 00:00:00.000' and '07/13/2004 23:59:59.999'
group by
vehicleName, companyName
order by
reportCount desc
<snip>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
