Thanks Unfortunately, simply having the two tables joined doesn't help.
The issue isn't getting the correct data, it's simply that to get the output I need, I have to loop over each column in each record to see which column has been updated in a given time period, and output the info about that column. USERS has a single "lastupdated" column, just so we know when it was updated. USER_UPDATED has most of the same columns as USERS, but each column just holds a date stamp of the last time the corresponding field was updated in USERS The data looks something like this: USERS: UserID Firstname Lastname ------ --------- ----------- 1 John Doe 2 Jane Smith USER_UPDATE: UserID Firstname Lastname ------ ---------- ----------- 1 07/08/2005 10/31/2006 2 05/10/2006 7/15/2006 The output I am looking for is this, given an input date of 01/01/2006: UserID FieldName Value ------- ----------- ------- 1 Lastname Doe 2 Firstname Jane 2 Lastname Smith The code produces the exact output I need, so the basic logic is fine (if not as fast as I need it to be). The queries run in a few milliseconds, so they aren't the bottleneck. It's the looping through each column in each record of the resultset to check the updated date of each field when it's 12000 records x 89 columns that's the bottleneck. > -----Original Message----- > From: Richard Kroll [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 09, 2007 1:26 PM > To: CF-Talk > Subject: RE: Getting changed fields from database using an audit table > > In a SQL query without the dynamic column list, is this what you are > trying to achieve? > > SELECT u.id > FROM users u > INNER JOIN users_updated up > ON u.id = up.id AND up.lastupdated >= <cfqueryparam > cfsqltype="cf_sql_date" value="#since#"> > WHERE u.id IN ( > SELECT id > FROM USER_UPDATE up1 > WHERE > u.cola <> up1.cola OR > u.colb <> up1.colb OR > u.colc <> up1.colc OR > etc <> etc > ) > > Rich Kroll > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266075 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
