WHERE NOT EXISTS (SELECT* FROM featureenable
WHERE main_db.FeatureKey = featureenable.FeatureKey);with
LEFT JOIN featureenable ON main_db.FeatureKey = featureenable.FeatureKey WHERE featureenable.FeatureKey IS NULL;
See the manual for more <http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html>.
Michael
Bartis, Robert M (Bob) wrote:
I have a query that I have put together. The query is an attempt to retrieve records from one table, main_db, whose keys are not present in another, featureenable. I am using the NOT EXISTS keywords and continue to receive an ODBC---Call Fail error. I traced the ODBC calls and see something very odd.
It appears from the trace that the query being passed from an MS Access front-end to the ODBC connector is being corrupted I've extracted the lines of interest shown below. Please note the MS2 referenced in the 2nd line. These are not in the original query I passed in. Why is the query being passed to the ODBC connector changed? The full queries are also shown.
.........EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey = featureenable.FeatureKey);
.........EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey
Original Query ================================================================================== SELECT DISTINCT [functionalsubgroup].[Functional], [functionalsubgroup].[SubGroup], [main_db].[Feature], [main_db].[FeatureKey], [functionalsubgroup].[FSKey] FROM featureenable INNER JOIN (functionalsubgroup INNER JOIN main_db ON ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey])) ON [featureenable].[FeatureKey]=[main_db].[FeatureKey] WHERE NOT EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey = featureenable.FeatureKey);
ODBC Call Trace ============================================================================
STRDB-v2.0b14 83c-218 ENTER SQLExecDirectW HSTMT 08DC1C30
WCHAR * 0x0F271F40 [ -3] "SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((((((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0"
SDWORD -3
STRDB-v2.0b14 83c-218 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 08DC1C30 WCHAR * 0x0F271F40 [ -3] "SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((((((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0" SDWORD -3
DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-standard]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`M (1064)
Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: <[EMAIL PROTECTED]> Pgr: <[EMAIL PROTECTED]>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
