possible to create sql query with table wildcards?

Question

this is probably a simple question, but i have been unable to find a solution online, any help would be much appreciated.

I'm trying to create an SQL query in PHP and would like to somehow apply a wild card to the TABLE filter... something perhaps like.... select * from %table%. However, I have only so far been able to see filters for column values not table names.

as an example i would have tables such as:

jan_table_1 feb_table_1 jan_table_2 feb_table_2

and would want to say, select only tables with a "jan" prefix... or "1" suffix.

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

Answer 1

There isn't. But the tables shouldn't be separated by month. Instead appropriate indexes should be used to speed up access.

Answer 2

The only way you can do that is by dynamically generating the SQL statement for a particular table and executing it.

The fact you want to do this suggests you should re-visit the design of your table schema.

Answer 3

You can't just use a wildcard when selecting from tables.

What you might be able to do is create a view over your "jan_*" or "*_1" tables and select from that.

You will need to update that view whenever tables are added.

Answer 4

In Sql server you can query for the table names you want like this

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

In your code you could loop through the table names and execute your query on each table and merge the results. Most other RDBMS have similar functionality.

Answer 5

You should not partition tables like this. Instead, consider putting it all in a unified table, with columns for month and index OR create a table with month and index columns, and reference a row id in your other 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;