file: Workbench Query Using Complex Variables
The following two EXAMPLES (QUERY-ONE(a & b) and QUERY-TWO) represent results
as used in MySQL WORKBENCH 6.3 (Version 6.3.6 Build 511 CE (64 bits)
QUERY-ONE-a WORKS with expected results when @org1 is NOT Used in query.
NOTE: "... AND cl_org_cd NOT IN (3,4,5,6,7,8,9) ); "
QUERY-ONE-b DOES NOT WORK with expected results when @org1 is Used in query.
NOTE: "... AND cl_org_cd NOT IN (@org1) ); "
The PROBLEM is "What is the correct construction of QUERY-ONE-a using php and or
MySQL Workbench to obtain a correct result when using @org1 variable
as illustrated in QUERY-ONE-b?
/* QUERY-ONE-a */
/*-------------------------------------*/
/* Workbench WORKS - @org1 NOT Used */
/* rows to EXCLUDE WHERE @org1= 1 or 2 */
/*-------------------------------------*/
SET @date = '2015-01-01';
SET @org1 = '(3,4,5,6,7,8,9)';
DROP TABLE IF EXISTS tmp_class;
CREATE TABLE IF NOT EXISTS tmp_class AS SELECT * FROM ce_class
WHERE cl_date >= @date;
SELECT cl_id, cl_date, cl_classname, cl_instructor1, cl_org_cd FROM tmp_class A
WHERE NOT EXISTS
(SELECT cl_org_cd FROM tmp_class B WHERE A.cl_id = B.cl_id
AND cl_org_cd NOT IN (3,4,5,6,7,8,9) );
/*-------------------------------------------------------------*/
/* Workbench Returns: Expected (4) records cl_org_cd = 4,3,3,9 */
/* cl_org_cd = '4' 1 record - OK */
/* cl_org_cd = '3' 2 records - OK */
/* cl_org_cd = '9' 1 record - OK */
/* EXCLUDES cl_org_cd = '1' 24 records - OK */
/* EXCLUDES cl_org_cd = '2' 117 records - OK */
/*-------------------------------------------------------------*/
/* QUERY-ONE-b */
/*---------------------------------------*/
/* Workbench DOESEN'T WORK - @org1 Used */
/*---------------------------------------*/
SET @date = '2015-01-01';
SET @org1 = '3,4,5,6,7,8,9';
DROP TABLE IF EXISTS tmp_class;
CREATE TABLE IF NOT EXISTS tmp_class AS SELECT * FROM ce_class
WHERE cl_date >= @date;
SELECT cl_id, cl_date, cl_classname, cl_instructor1, cl_org_cd FROM tmp_class A
WHERE NOT EXISTS
(SELECT cl_org_cd FROM tmp_class B WHERE A.cl_id = B.cl_id
AND cl_org_cd NOT IN (@org1) );
/*-------------------------------------------------------------*/
/* Workbench Returns: Unexpected (2) records where */
/* cl_org_cd = '3' 2 records - OK */
/* MISSED cl_org_cd = '4' 1 record - WRONG */
/* MISSED cl_org_cd = '9' 1 record - WRONG */
/* EXCLUDES cl_org_cd = '1' 24 records - OK */
/* EXCLUDES cl_org_cd = '2' 117 records - OK */
/*-------------------------------------------------------------*/
/* QUERY-TWO */
/*-----------------------------------*/
/* Workbench WORKS - @org1 Used */
/* EXCLUDE all rows where org1 = 2; */
/*-----------------------------------*/
SET @date = '2015-01-01';
SET @org1 = '2'; /* rows to EXCLUDE */
DROP TABLE IF EXISTS tmp_class;
CREATE TABLE IF NOT EXISTS tmp_class AS SELECT * FROM ce_class
WHERE cl_date >= @date;
SELECT * FROM tmp_class WHERE cl_date >= @date AND cl_org_cd != @org1;
/*-------------------------------------------------------------*/
/* Workbench Returns: CORRECT (28) records where returned */
/* cl_org_cd = '1' 24 records - OK */
/* cl_org_cd = '3' 2 record - OK */
/* cl_org_cd = '4' 1 record - OK */
/* cl_org_cd = '9' 1 record - OK */
/* */
/* EXCLUDES cl_org_cd = '2' 117 records - OK */
/*-------------------------------------------------------------*/
If you have an answer - Please respond to
wimbre042@gmail.com
Thanks