ofarukcaki ofarukcaki - 1 year ago 322
MySQL Question

MySQL SELECT * FROM table1,table2,table3

I have several tables with same structure. All I want is get all results from all tables from a database.

For example:

Table1:

id | Name
-----------
1 | John
2 | Alex
3 | Patrick
4 | Donald
5 | Jane


Table2:

id | Name
-----------
1 | Ben
2 | Dale
3 | Kane
4 | Tom
5 | George


My desired result with SELECT Name FROM ... is:

Name
-------
John
Alex
Patrick
Donald
Jane
Ben
Dale
Kane
Tom
George


Is there any way to do this easily without using UNION like: SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT * FROM table3....

Because this makes my query so long and it is pain for me.Probably I'll have more than 20-30 tables in a database. I'm looking for somethings like SELECT * FROM table1,table2.. etc.

Thanks.

UPDATE




I did it like this (in PHP), I hope it also helps for others:

$dbc1 = $db->query("SELECT group_concat(table_name) AS tables FROM information_schema.tables WHERE table_schema='dbname'");


This query return all table names as 1 string with comma seperated like this : table1,table2,table3....
Then I explode that string and I used in foreach loop.

$dbc1_ftchd = $dbc1->fetch(PDO::FETCH_ASSOC);
foreach(explode(',',$dbc1_ftchd['tables']) as $next_tb_name){
echo $next_tb_name.'<br>';
}


Now I able to use all tables as I want one by one instead of use all of them at the same time. But no problem it is return same result for me.

Thanks to everyone who helped me :)

Answer Source

This should work:

SELECT `Name` FROM `Table1` 
UNION
SELECT `Name` FROM `Table2` 
UNION
SELECT `Name` FROM `Table3` 

EDIT: Sorry, I didn't saw the last paragraph of your question. You can create a view or temporary table if you don't like UNIONs that much.

Create View: https://dev.mysql.com/doc/refman/5.7/en/create-view.html

Create Temporary Table: CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table AS (SELECT * FROM table1 UNION ...)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download