Because the column name will be represented by whatever the FIRST select is,
and in this case, that is 'top_shooter_class'. Try this:
<cfquery name="get_results" datasource="#application.dsn#">
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
and r.class in ('A', 'AA', 'AAA')
and r.tournament_id = t.tournament_id
and t.tournament_id = <cfqueryparam cfsqltype="integer"
value="#url.tournament_id#">
order by class, broke desc
union all
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
and r.class not in ('A', 'AA', 'AAA')
and r.tournament_id = t.tournament_id
and t.tournament_id = <cfqueryparam cfsqltype="integer"
value="#url.tournament_id#">
order by class desc, broke desc
</cfquery>
If that doesn't work, try some parentheses around each of the selects. If
that still doesn't work, last resort you can do this:
<cfquery name="get_results1" datasource="#application.dsn#">
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
and r.class in ('A', 'AA', 'AAA')
and r.tournament_id = t.tournament_id
and t.tournament_id = <cfqueryparam cfsqltype="integer"
value="#url.tournament_id#">
order by class, broke desc
</cfquery>
<cfquery name="get_results2" datasource="#application.dsn#">
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
and r.class not in ('A', 'AA', 'AAA')
and r.tournament_id = t.tournament_id
and t.tournament_id = <cfqueryparam cfsqltype="integer"
value="#url.tournament_id#">
order by class desc, broke desc
</cfquery>
<cfquery dbtype="query" name="get_results">
Select * from get_results1
Union all
Select * from get_results2
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311614
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
