Mysql SELECT and UNION multiple tables with a similar name

mysql union
mysql union all
how to combine two sql queries in one result
sql multiple select statements one result
sql combine two queries with different columns
sql merge two tables with same columns
how to retrieve data from two tables with one sql statement
mysql union order by

I was wondering if it is possible to create a dynamic Union query for all tables with the same name but with a different number at the end. I have created a system but with each user having their own table such as:

user_table_$userID

I have achieved this in PHP but really would like to create a more dynamic code. I currently have 2-3 nested queries to grab the posts from each table without putting strain on the web server or database.

I suppose I could count the number of users in the user login table and create a for loop:

for ($i = 1; $i >= $usrCount; $i++)
{
    $queryArray[] = "(SELECT post_title, post_description FROM user_table_" . $i . ") UNION";
}

But if the user count is a very large number the PHP script could take a long time to load. Is there any way I could get the Mysql database to create a dynamic query based on tables with the name like = "user_table_%"

If there are any suggestions please let me know.

Thank you.

You can't and even if you find a clever hack you shouldn't.

Your best bet is to think again about your database schema. I assure you that your current structure is BAD, very very BAD. What if by chance you develop the new facebook? will you have ONE BILLION TABLES?

search for google about "database normalization" and "3rd normal form"

MySQL UNION: Combining Results of Two or More Queries, MySQL UNION operator allows you to combine two or more result sets of queries the orders of columns that appear in all SELECT statements must be the same. The following statement combines result sets returned from t1 and t2 tables: It uses the CONCAT() function to concatenate first name, space, and last name� This example uses the column heading of the first query for the output. It uses the CONCAT() function to concatenate first name, space, and last name into a full name. MySQL UNION and ORDER BY. If you want to sort the result set of a union, you use an ORDER BY clause in the last SELECT statement as shown in the following example:

Maybe it's better to normalize your database, but if you need a dynamic query you could use this:

SELECT
  GROUP_CONCAT(
    CONCAT(
      'SELECT * FROM `',
      TABLE_NAME,
      '`') SEPARATOR ' UNION ALL ')
FROM
  `INFORMATION_SCHEMA`.`TABLES` 
WHERE
  `TABLE_NAME` REGEXP '^user\_[0-9]*$'
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.

Performing Multiple-Table Retrievals with UNION, mysql> SELECT i FROM t1 UNION SELECT i FROM t2 UNION SELECT The names for the columns of the UNION result come from the names different results, even though they select the same values from the two tables:. The SQL UNION Operator. The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns; The columns must also have similar data types; The columns in each SELECT statement must also be in the same order; UNION Syntax

Seems like you should have only one table user_table containing a user_id field.

Now if you still want to keep your current model and need MySQL to build your query, maybe you could:

SHOW TABLES LIKE "user_table_%";

Create a temporary table and do INSERT SELECT for each table.

But again, it's barely advised to say the least

MySQL 8.0 Reference Manual :: 13.2.10.3 UNION Clause, UNION combines the result from multiple SELECT statements into a single result ROW(5,9) UNION SELECT * FROM t2; SELECT * FROM t1 UNION TABLE t2; names to be the same, wrap the VALUES on the left hand side in a SELECT� Result Set Column Names and Data Types. The column names for a UNION result set are taken from the column names of the first SELECT statement.. Selected columns listed in corresponding positions of each SELECT statement should have the same data type.

First normalized your table,for the time being you can run this query

SELECT post_title, post_description FROM (select table_name from INFORMATION_SCHEMA where table_name like 'user_table_%';

Combine results from several SQL tables, Databases; SQL � Oracle / PLSQL � SQL Server � MySQL � MariaDB � PostgreSQL � SQLite Each SELECT statement within the UNION ALL must have the same number of SELECT expression1, expression2, expression_n FROM tables [ WHERE In this SQL UNION ALL example, since the column names are different� I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT

This should work fine (but consider "database normalization")

for ($i = 1; $i >= $usrCount; $i++){
  $queryArray[$i] = "(SELECT post_title, post_description FROM user_table_".$i.")";
}    
$sql = implode(' UNION ', $queryArray);

SQL: UNION ALL Operator, The MySQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. [DISTINCT] SELECT expression1, expression2, expression_n FROM tables since the column names are different between the two SELECT statements,� To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION. The columns returned by the SELECT statements must have the same or convertible data type, size, and be the same order. The database system processes the query by executing two SELECT statements first.

MySQL: UNION Operator, Put differently, UNION allows you to write two separate SELECT statements, and one statement display in the same table as the results from the other statement. While the column names don't necessarily have to be the same, you will find� The UNION is a good solution for the tables as they are, but there should be no reason they can't be put into the one table with decent indexing. I've seen adding the correct index to a large table increase query speed by three orders of magnitude.

SQL UNION, To combine two or more SELECT statements to form a single result table, use one name, the nth column of the result table has that same result column name . However, if a column name appears in multiple tables, references to the column must be qualified by the table name using tbl_name.col_name syntax to specify which table you mean. Suppose a table mytbl1 contains columns a and b , and a table mytbl2 contains columns b and c .

Combining result tables from multiple SELECT statements, Yes, there is a less redundant/verbose way -- PARTITION a single table by year. Then a single SELECT will do the job. But, that may not even be wise. The easiest way to do this across multiple tables would be a UNION: (SELECT id, username, datetime FROM table 1 WHERE username = 'Some Name') UNION (SELECT id, username, datetime FROM table 2 WHERE username = 'Some Name') UNION (SELECT id, somethingelse AS username, date as datetime FROM table 3 WHERE somethingelse = 'Some Name') .

Comments
  • First of all it seems like unnormal database schema to create table for each user
  • I think the obvious suggestion would be to normalise your schema so you don't have a table for every user in your system.
  • try stored procedure also performance get improve
  • A table for each user sounds like madness to me.
  • I have a better idea. Because posts will be dealt with by category I suppose I could just make tables based on category and UNION those instead.
  • sure, the point of this answer is to make you rethink your database schema. Mission accomplished!
  • I'm going with STT LCU with this one because handling hundreds of tables is a bad idea.#