MySQL: Choosing multiple tables based on number of columns

Question

I have a database that contains several different types of surveys. Each survey has it's own table in the database. Some surveys have the same structure. These have the same number of columns. I want to choose a subset of the surveys based on what the table name contains and the number of columns.

There surveys I want can have two different names and they all contain numbers in the name:

SELECT table_name FROM information_schema.TABLES 
WHERE ((TABLE_NAME like '%ls_survey_%') or (TABLE_NAME like '%ls_old_survey_%')) 
and TABLE_NAME rlike '[0123456789]'

So far so good. I know that I can get the number of columns in a table thus:

SELECT count(*) FROM information_schema.COLUMNS WHERE table_name = 'tableName'

I a stumped as to how to combine the two expressions above to get only tables that satisfy the first expression and that have only a given number of columns.

Answer 1

It is certanly possible, you can query the COLUMNS table, and use a GROUP BY query with an HAVING clause:

SELECT `table_name` 
FROM
  `information_schema`.`columns` 
WHERE
  `table_schema`=DATABASE() 
  AND (`table_name` LIKE '%ls_survey_%' OR `table_name` LIKE '%ls_old_survey_%')
  AND `table_name` RLIKE '[0123456789]'
GROUP BY
  `table_name`
HAVING
  COUNT(*)=3 -- number of columns

However I would suggest you to try to rethink your database structure: it is often not a good idea to have a lot of similar tables, one for every survey, in your database. An idea would be to store your survey like this:

ID survey | Question   | Answer
--------------------------------
3456      | question1? | ....
3465      | question2? | ....
3456      | question3? | ....
7777      | question1? | ....
7777      | question4? | ....
7777      | question5? | ....