Select query for multiple tables

Question

I have tables A01, B01, C01 and so on... all with identical structure.

+----+-----+
| ID | val |
+----+-----+
|  1 | a   |
|  2 | b   |
+----+-----+

+----+-----+
| id | val |
+----+-----+
|  1 | c   |
|  2 | d   |
+----+-----+

+----+-----+
| id | val |
+----+-----+
|  1 | e   |
|  2 | f   |
+----+-----+

Is it possible to have a query that displays the "val" column from all tables ?

Also so that if a new tables is created the query should not be changed.

I am going to run this on a PHP webpage.

No. It's not possible. Normalise your design. – Strawberry Nov 01 '13 at 10:17 — Strawberry, Nov 01 '13 at 10:17

Naveen Kumar Alone · Answer 1 · 2013-11-01T11:38:17.860

1

SQLFiddle

SELECT t1.val
  FROM table1 AS t1
UNION
  SELECT t2.val FROM table2 AS t2
UNION
  SELECT t3.val FROM table3 AS t3

edited Nov 01 '13 at 11:38

answered Nov 01 '13 at 10:09

Naveen Kumar Alone

7,536
5
36
57

@echo_Me im doing on the way to using UNION, but before i have tried with inner join, then how could it to be same? :( – Naveen Kumar Alone Nov 01 '13 at 10:33

echo_Me · Answer 2 · 2013-11-01T10:47:56.807

1

you cant do new tables in mysql . you must define them . for the given tables you can do this

   select val from `A01`
   union all
   select val from `B01`
   union all
   select val from `C01`

demo

edited Nov 01 '13 at 10:47

answered Nov 01 '13 at 10:22

echo_Me

37,078
5
58
78

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Create a view with union or union all clause with all your required tables. If you create a table in future , just alter the view to include that table as well.

CREATE VIEW AS MYVIEW
SELECT VAL FROM A01 UNION ALL
SELECT VAL FROM B01...

Always use same query to get data:

SELECT VAL FROM MYVIEW

edited May 23 '17 at 10:25

Community

1
1

answered Nov 01 '13 at 10:31

Praveen Prasannan

7,093
10
50
70

All answers are good, had to mark yous as best due to vieuw suggestion – Axxess Nov 01 '13 at 11:36

Select query for multiple tables

3 Answers3