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
