Can I create a sql query using table wildcards?

Question

This is probably a simple question, but I could not find a solution on the Internet, any help would be greatly appreciated.

I am trying to create a SQL query in PHP and would like to somehow apply a wild card to the TABLE filter ... something like ... select * from %_table_% . However, I still could see filters for column values, not table names.

as an example, I would have tables like:

 jan_table_1 feb_table_1 jan_table_2 feb_table_2 

and I’d like to say, select only tables with the prefix "jan" ... or "1".

Is there a quick and easy solution that I have not seen? Thanks in advance!

Answer 1

Not. But tables should not be divided by month. Instead, use appropriate indexes to speed access.

Answer 2

On the Sql server, you can query for the names of the tables you want, such as

 select * from sys.tables where name like '%table%' 

In your code, you can scroll through the table names and execute your query on each table and combine the results. Most other DBMSs have similar features.

Answer 3

You should not split tables in this way. Instead, consider placing all in a single table with columns for the month and index OR create a table with columns for the month and index and specify the row identifier in another table:

Option 1: unified table:

 CREATE TABLE Unified ( month CHAR(3) NOT NULL, ix INT NOT NULL DEFAULT 1, [...], PRIMARY (month, ix, somethingMore), CHECK month IN ( 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec') ); SELECT * FROM Unified where month = 'jan' AND ix = 1; // select only tables with a "jan" prefix... or "1" suffix. SELECT * FROM Unified where month = 'jan' OR ix = 1 

Option 2: use a foreign key:

 CREATE TABLE Partitions ( id INT AUTO_INCREMENT PRIMARY, month CHAR(3) NOT NULL, ix INT NOT NULL DEFAULT 1, CHECK month IN ( 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'), INDEX (month, ix) ) CREATE TABLE Stuff ( partition INT NOT NULL, [...], PRIMARY KEY (partition, somethingMore), FOREIGN KEY fk_Stuff_Partitions (partition) REFERENCES Partitions (id) ) SELECT * FROM Stuff INNER JOIN Partitions ON Stuff.partition = Partitions.id WHERE Partition.month = 'jan' AND Partition.ix = 2; // select only tables with a "jan" prefix... or "1" suffix. SELECT DISTINCT * FROM Stuff INNER JOIN Partitions ON Stuff.partition = Partitions.id WHERE Partition.month = 'jan' OR Partition.ix = 1; 

Answer 4

The only way to do this is to dynamically generate an SQL statement for a particular table and execute it.

The fact that you want to do this suggests that you should revisit the design of your table schema.

Answer 5

You cannot just use a wildcard when selecting from tables.

What you can do is create a view above the "jan_*" or "*_1" tables and select from it.

You will need to update this view when tables are added.