Depending on your DBMS, I would suggest declaring a temp table or table variable (depending on how many records you are expecting).
Do two separate inserts into the temp table. Then update any additional info you need into the table if applicable, (This allows you to have a smaller, better performing initial select). Then select out the final records from the temp table. If it takes you 50 seconds to run that select, you probably have some DB tuning to do. Are there indexes on the expen_Date and pur_Date_Purchased columns. ~Brad -----Original Message----- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Friday, September 21, 2007 12:01 PM To: CF-Talk Subject: table union I am trying to create a report that will show me all the checks I have written on my system in numerical order, the problem is I have my checks stored in two different tables in the database. These two tables split up expense checks and client checks, both tables have quite different data, however they do share some common data (check_Num), Ammount, and a date drafted, the other data isn't the same data types because I use a customer ID(number) to pull the name of the client for the check and I just type the name of the recipient(text) for the expense checks. Right now I am able to get all the checks in order with the following query. ----------------------------------- <cfquery name="rsGetPayments" datasource="# dsn#"> SELECT Check_Num, CheckAmount, expen_Date FROM tbl_Expenses WHERE expen_Date BETWEEN #CreateODBCDateTime(cFullStartDate)# AND #CreateODBCDateTime(cFullEndDate)# UNION SELECT Check_Num, pur_Amount, pur_Date_Purchased FROM tbl_HCFA_Purchase_Transactions WHERE pur_Date_Purchased BETWEEN #CreateODBCDateTime(cFullStartDate)# AND #CreateODBCDateTime(cFullEndDate)# ORDER BY Check_Num </cfquery> This gets me a unioned list sorted by check number in a date range supplied by a form. My question, is there a better way to union this data together because right now after I run this query I am going to have to run two separate queries in each table to get who the check was made out to and what the expense code was for the check. Right now this one query takes about 50 seconds to run and I imagine will only get longer with how many checks I write. Thanks in advance, Mallek ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289111 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
