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]
