in my table 'entites' i store some tablenames in column 'tablename' of the used database.
Now i want to get all tablenames of the database who are not in entites.tablename
Here is my statement:
WHERE table_schema = 'codeigniter'
AND table_name COLLATE utf8_general_ci NOT IN (
(SELECT concat(tablename) FROM codeigniter.entities)
`id` int(32) NOT NULL AUTO_INCREMENT,
`tablename` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Your subquery returns a single concatenation string of all* tables in the "codeigniter" schema; unless you expect entities.table to be such a concatenation, you won't get the results you want.
It is the difference between
A IN ("B", "C", "D") AND
A IN ("B,C,D")
*"ALL" as in all that the setting for the max length of a group_concat call will allow to fit.
My guess is you simply need to not use