ok thanks brad, i have taken it out of the from list, you learn something new everyday
i hadnt realised that by putting that in there that it would think that i am joining it, even if i had not specified any columns from that query but would still attempt to join it. to be honest i thought i needed it to do the valuelist stuff as you said. it has sped it up thanks. you stated that i hadnt added any join clauses but as i think you guessed there was not meant to be a join thanks brad >Why is the mergedColumnsQuery included in the from clause? >You are not using any of its columns in the select clause, nor are you >filtering it in the where clause. Not only, but you again have not >specified any join criteria to join it to the "query" query. (perhaps >there's a better name for that one). > >You do not need to have the mergedColumnsQuery query in the from list to >do the valuelist() stuff. > >~Brad > >i am getting the exact same problem with the following query but cannot >see why it is taking so long and what is missing - would also appreciate >your help with this one: > ><cfquery name="" dbtype="query"> > select >query.subjectID,query.projectID,query.testOccasionID,query.subjectHeight >,query.subjectWeight > > from query, mergedColumnsQuery > > where query.subjectID NOT IN >(#ValueList(mergedColumnsQuery.subjectID)#) > and query.projectID NOT IN (#ValueList(mergedColumnsQuery.projectID)#) > and query.testOccasionID NOT IN >(#ValueList(mergedColumnsQuery.testOccasionID)#) ></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:308395 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4