CODEWITHSUNDEEP
mysql
Will
Will

Reputation: 13

MySQL Selecting From a Table name matching string

I'm relatively new to SQL and I've been having trouble trying to figure out how to select rows from a table that matches a string. The name of the table uses the current month and year. It looks like this: 

XXXX.xxx_YY_MM

where XXXX is the database, YY is year, and MM is month. So normally the query would just look like this:

select * from XXXX.xxx_16_05;

However, I want it to be able to change depending on the date. So I thought this would work:

select * from (select concat('XXXX.xxx_',date_format(now(), '%y_%m'))));

The concat bit gives me something that looks exactly like the name of the table. But it doesn't work and I'm not sure why. It says every table must have it's own alias. I'm not sure what to do about it.

Alternatively, I was thinking maybe something like this would be ok

 select * from  (select * from information_schema.tables where table_name like concat('%logsms_',date_format(now(), '%y_%m'),'%'));

But it doesn't work either. What should I do? There is only one table with a name that matches the string.

Upvotes: 1

Views: 3445

Answers (2)

Radadiya Nikunj
Radadiya Nikunj

Reputation: 1085

Please find the following query to retrieve all table by matching string.

select table_schema as database_name,table_name from information_schema.tables
where table_name like '%your_table_name%' order by table_schema, table_name;

Upvotes: 0

Barmar
Barmar

Reputation: 781068

You can't use an expression for the table name in SQL. You need to use a stored procedure that creates dynamic SQL and executes it using PREPARE and EXECUTE.

SET @sql = CONCAT('SELECT * FROM XXXX.xxx_', DATE_FORMAT(NOW(), '%y_%m'));
PREPARE stmt FROM @sql;
EXECUTE stmt;

A database design that requires this seems like a poor decision. You should have a single table where the date is in a column, not separate tables for each month.

If you're running the query from a programming language, you can use its own date functions to constructure SQL.

Upvotes: 3

Related Questions