You need to alias the columns in the original queries to they both match, and you must have same number of colums, so exclude the ones that don't exist in both tables.
Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 29, 2013 9:01 PM, "Les Mizzell" <[email protected]> wrote: > > I have two separate queries, basically a table of NEW stuff, and then a > table of OLD stuff that's not been converted to new yet. Not all fields > in the two tables match. > > So, first query finds all the matching NEW things > Second query finds all the OLD things that aren't present in the NEW > things query. > > Works so far and both queries output what I'm expecting. > > Now, I need to MERGE the results of both, and sort them by a common field. > > Below will NOT work, because there's a different number of columns/blah > being returned: > > <cfquery name="results" dbType="query"> > *SELECT* > first_name, > last_name, > off1_name, > off2_name, > off3_name, > atty_id, > email > FROM newstuff > UNION > SELECT > first_name, > last_name, > office_name, > atty_id, > email > FROM oldstuff > ORDER BY last_name > </cfquery> > > Generates this error: > *Query Of Queries runtime error.* > All queries in an SQL SELECT statement that contains a UNION operator > must have an equal number of expressions in their result list. > > Is there a way around this? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354108 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
