0

I have lots of tables named item1, item42, etc. (there are "holes" in the numbering). All tables have the same columns. In particular they have a column called Value.

How do I retrieve in mySQL the list of all tables for which all their tuples have the same value for Value?

SELECT Count(Value) FROM item1 GROUP BY Value;

allows to find the number of different values in Value for a table.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myschema'

allows to find all the tables I want to go through.

How do I combine those to get the list of tables for which that count is strictly greater than 2?

(I am not asking whether this database structure is good or bad, it is what it is.)

Fatalize
  • 3,513
  • 15
  • 25
  • Fix your data structure so you are not storing the same entities in multiple tables. Parallel table structures are usually a sign of problem with the data modeling and lead to issues with querying. – Gordon Linoff Mar 08 '17 at 13:38
  • @GordonLinoff Did you even read the last line? I have no control over the structure. – Fatalize Mar 08 '17 at 13:39
  • Can you use dynamic SQL? http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure – Neville Kuyt Mar 08 '17 at 13:42
  • 1
    Yes, did read it (and would have even without the bold). That is really the right solution if the database is going to be used effectively. Barring that, you could create a view to do a `union` of all the tables. – Gordon Linoff Mar 08 '17 at 13:43
  • @NevilleK Possibly in a procedure. – Fatalize Mar 08 '17 at 13:43

0 Answers0