0

I'm trying to craft a straight SQL (not stored procedure) query that finds all databases in a MySQL database server with a table whose name matches a pattern and has a certain collation, and then use the list of those database names and table names to get a specific value out of the tables that match the pattern.

For this query:

  • I do not know the names of the databases on the server (they must be queried)
  • I do not know the names of the tables in the database that could match (they must be compared against a pattern of %options)

For example, if I have 3 databases:

DB1 with tables:

  • something
  • f_options (containing a row where the value for a column called option_name is 'test' and the value for the option_value column is 'y')

DB2 with tables:

  • something

DB3 with tables:

  • something_else
  • zeoptions (containing a row where the value for a column called option_name is 'test' and the value for the option_value column is 'z')

Then I want a query that:

  1. Finds databases with tables who match the collation and naming scheme
  2. Select the values of the rows where the column called option_name has a row whose value is "test" in the column

For example, in the above 3 database tables, it would see Database 1 and 3 have a matching table, and it would return the values 'y' and 'z'

I'm trying to do this without using stored procedures or views (just a simple SQL statement).

So far I've gotten a working system that does 1. By querying the MySQL information_schema table, I can get the names of the databases + matching tables using this:

SELECT s.dbname, s.tablename FROM ( SELECT 
`information_schema`.`TABLES`.`TABLE_SCHEMA` dbname ,  
`information_schema`.`TABLES`.`TABLE_NAME` tablename FROM  
`information_schema`.`TABLES` WHERE `information_schema`.`TABLES`.`TABLE_NAME` LIKE 
 '%options' AND `information_schema`.`TABLES`.`TABLE_COLLATION` LIKE  
'utf8mb4_unicode_ci' ) as s;

This query works

Now the problem is figuring out how to write a SELECT statement who uses the above SQL statement as a subquery to populate the FROM

Right now this is what I have (though there's an obvious SQL error in how the FROM statement currently works). If I can solve the FROM statement issue I can finish the rest

SELECT `s.dbname`.`s.tablename`.`option_value` FROM  `s.dbname`.`s.tablename`
( SELECT `information_schema`.`TABLES`.`TABLE_SCHEMA` dbname , 
`information_schema`.`TABLES`.`TABLE_NAME` tablename FROM 
`information_schema`.`TABLES` WHERE 
`information_schema`.`TABLES`.`TABLE_NAME` LIKE '%options' AND
 `information_schema`.`TABLES`.`TABLE_COLLATION` LIKE 'utf8mb4_unicode_ci' ) as s
WHERE `s.dbname`.`s.tablename`.`option_name` LIKE 'test';

Which can be simplified to

SELECT `s.dbname`.`s.tablename`.`option_value` FROM  `s.dbname`.`s.tablename`
( the working query above ) as s
WHERE `s.dbname`.`s.tablename`.`option_name` LIKE 'test';

Does anyone know how I can fix the 

 FROM  `s.dbname`.`s.tablename` ( SELECT ... )  as s

part?

Chris
  • 1,881
  • 3
  • 20
  • 27
  • You missed one FROM before ( – bksi May 20 '15 at 19:51
  • @bksi I'm confused about your response. Can you explain it further please? – Chris May 20 '15 at 19:53
  • If you use SELECT you have to use FROM ... You use SELECT `s.dbname`.`s.tablename`.`option_value` FROM `s.dbname`.`s.tablename` ( SELECT....... You have to have FROM after `s.dbname`.`s.tablename` – bksi May 20 '15 at 19:54
  • @bksi I do, it's just down a line because the table name information_schema is long. There's 2 SELECT's and 2 FROM's – Chris May 20 '15 at 19:55
  • Oh, then you have to use kind of join. you can use the simple "," (cross join) – bksi May 20 '15 at 19:56
  • FROM `s.dbname`.`s.tablename` `,` ( SELECT `information_schema`.`TABLES`.`TABLE_SCHEMA` dbname , – bksi May 20 '15 at 19:58
  • But then you have to put the join clause you need after second WHERE – bksi May 20 '15 at 19:58
  • 1
    I am pretty sure it cannot be done with a single query. Even with multiple queries, you would need dynamic query construction to retrieve the actual values. – Uueerdo May 20 '15 at 20:02
  • Sorry mate i don't think you can acheive that with single query – bksi May 20 '15 at 20:02
  • I agree with the others, you can't do this with a simple query. You need to write a stored procedure that uses a query against `INFORMATION_SCHEMA` to construct a SQL string, then use `PREPARE` to turn that into a query, and `EXECUTE` to execute it. – Barmar May 20 '15 at 20:07

0 Answers0