I was out of the office on Friday, but this worked like a charm. Thanks for all your help.
On the name thing, I'm just following SOP here. If you can make someone else have a potty mouth, do it (it's great fun in meetings). At least I don't need a header table or variable (maybe I can work that in later). -----Original Message----- From: Christopher Olive [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:20 AM To: CF-Talk Subject: RE: Query a Query or SQL Syntax (Not IN) should you have a ValueList(asstaken.assname) in your last recordset? as it is, you're trying to exclude a *query variable#, not a list of names. IE: <cfquery name="AssNonComp" dbtype="query" > Select empl from AssList,asstaken Where empl NOT IN (#ValueList(AssTaken.assname)#) </cfquery> as a side note, very...interesting variable naming. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -----Original Message----- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:01 AM To: CF-Talk Subject: Query a Query or SQL Syntax (Not IN) I've got two queries from different data sources, and I want to find records in the First, that are not in the second (a non-compliance report) Shouldn't I be able to do a NOT IN in a Query of Query? My code is as follows (Ass is short for Associate): <!--- MS SQL Database - the recordset of people that have taken the test Right now record count is 0 ---> <cfquery name="AssTaken" datasource="Intranet">Select AssName from Intranet.PBS.App_StraightTalk</cfquery> <!--- Informix Database - the recordset of all the people that need to take the test Right now record count is 2160---> <cfquery name="AssList" datasource="INF_OL7" cachedwithin="#CreateTimeSpan(0, 6, 0, 0) #">Select empl,locnum,first_name,last_name,last_hire From associate Where last_hire > '12/24/2001'</cfquery> <!--- The non-compliance recordset This returns 0 records---> <cfquery name="AssNonComp" dbtype="query" >Select empl from AssList,asstaken Where empl NOT IN (AssTaken)</cfquery> What am I doing wrong, or how should I do this? ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
