I love your field names "AssName"! Brian Yager President - North AL Cold Fusion Users Group Sr. Systems Analyst NCCIM/CIC [EMAIL PROTECTED] (256) 842-8342
-----Original Message----- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 10: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? ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
