Thanks for the tip. I'm still facing an issue where I think I have the right syntax and I'm receiving an ODBC failure. Do you have any suggestions on how to go about understanding why the failure and hence how to correct it?
Bob -----Original Message----- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 9:30 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Finding records not in a set Keep in mind EXISTS/NOT EXISTS are for SQL (Parent) Heavy queries Otherwise you should use IN/NOT IN Martin Gainty 617-852-7822 Man1: In my next life I want to get paid for solving problems Man2: You sound like a misguided capitalist! Man1: But how do I pay my bills?? Man2: You can always beg on the street..problem solved >From: "Bartis, Robert M (Bob)" <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: RE: Finding records not in a set >Date: Sat, 9 Oct 2004 08:53:19 -0400 MIME-Version: 1.0 >Received: from lists.mysql.com ([213.136.52.31]) by mc7-f3.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Sat, 9 Oct 2004 05:54:52 -0700 >Received: (qmail 20728 invoked by uid 109); 9 Oct 2004 12:53:26 -0000 >Received: (qmail 20709 invoked from network); 9 Oct 2004 12:53:26 -0000 >Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates >192.11.226.161 as permitted sender) >X-Message-Info: JGTYoYF78jEAJ70xKNiMjuhlQYGFj9no >Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm >List-ID: <mysql.mysql.com> >Precedence: bulk >List-Help: <mailto:[EMAIL PROTECTED]> >List-Unsubscribe: ><mailto:[EMAIL PROTECTED]> >List-Post: <mailto:[EMAIL PROTECTED]> >List-Archive: http://lists.mysql.com/mysql/173887 >Delivered-To: mailing list [EMAIL PROTECTED] >Message-ID: ><[EMAIL PROTECTED]> >X-Mailer: Internet Mail Service (5.5.2657.72) >X-Virus-Checked: Checked >Return-Path: [EMAIL PROTECTED] >X-OriginalArrivalTime: 09 Oct 2004 12:54:52.0865 (UTC) >FILETIME=[2B57B310:01C4ADFF] > >I did find a reference to EXIST/NOT EXISTS clause in the SQL manual, but I >get an ODBC Failed call when I run the following simplified query > >SELECT * >FROM main_db >WHERE NOT EXISTS (SELECT * FROM featureenable WHERE >featureenable.FeatureKey = main_db.FeatureKey); > >Bob > >-----Original Message----- >From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] >Sent: Saturday, October 09, 2004 8:48 AM >To: [EMAIL PROTECTED] >Subject: Finding records not in a set > > >I have a query, call qry_AssociatedFeatures, that finds all features >associated with a given plan > >SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, >main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, >featureenable.PlanName >FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON >main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = >main_db.FeatureKey >WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); > >So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, >or all the features not associated with a plan. Complication here is the >feature enable table can have the same feature key associated with multiple >plans. I assumed if I took the table containing the unique set of features >and query for those records whose feature key is not present in the >qry_AssociatedFeatures query I would get what I wanted > >SELECT DISTINCT main_db.Feature, main_db.FeatureKey >FROM main_db, qry_AssociatedFeatures >WHERE (((main_db.FeatureKey)<>[qry_AssociatedFeatures].[FeatureKey])); > >Unfortunately, I still see records in the qry_UnassociatedFeatures that are >also present in the qry_AssociatedFeatures. Any suggestions? > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > _________________________________________________________________ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
