0

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:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'codeigniter'
AND table_name COLLATE utf8_general_ci NOT IN (
    (SELECT concat(tablename) FROM codeigniter.entities)
)

This works fine, but i'm not shure that this is th best practice:

My database and all tables and columns has 'utf8_unicode_ci' collation, the information_schema has 'utf8_general_ci' collation.

structur of table 'entites' in database codeigniter :

(
    `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 
AUTO_INCREMENT=13 ;
Chrobak Stefan
  • 23
  • 5

2 Answers2

0

I don't really understand your table structure, but try this:

SELECT DISTINCT entities FROM entities.table WHERE NOT EXISTS (SELECT NULL FROM t2 WHERE table_schema = 'codeigniter')

Source: mysql SELECT NOT IN () -- disjoint set?

Community
  • 1
  • 1
Theo Orphanos
  • 1,417
  • 1
  • 19
  • 27
0

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 GROUP_CONCAT.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21