How to union multiple table in MYSQL

Question

How can I union multiple table in mysql and order by date?

I have 3 differents tables and I want to make a query to get all data inside that and order by date

SELECT * FROM table1, table2, table3 ORDER by date DESC


TABLE 1 

ID  |  USER ID | DATE | 
 2      4        2018

TABLE 2 

ID  |  CAR ID | DATE | 
 3       9      2017

TABLE 3

ID  |  AIR ID | DATE | 
 4      6        2019

I expected result like this

ID | ALLID | DATE
2      4      2018
3      9      2017
4      6      2019

Show us a few rows of sample table data, and also the expected result. (As formatted text, not images.) — jarlh, Sep 02 '19 at 20:13
Since you mention UNION in your question didn't you google it? — forpas, Sep 02 '19 at 20:26

score 0 · Accepted Answer · answered Sep 02 '19 at 21:02

The correct syntax for union is union/union all. A comma in the from clause is not correct syntax . . . well, for anything.

So:

select id, allid, date from table1
union all
select id, allid, date from table2
union all
select id, allid, date from table3
order by date desc;

score 0 · Answer 2 · answered Sep 02 '19 at 21:10

You would not be able to create your 2nd column ALLID, however you could use Join to create the tables, but the search results would be difficult if you are trying to search by ID. Can you give more context.

TO Join Table 1 and Table 2 together you need to have a primary key and a foreign key, so a column from table 1 and a cloumn from table 2 that both appear in table 3.

SO you can use ID from Table 1 and Date from table 2 because both are in table 3 so these are your Join accessories: basic join: all data

     SELECT * FROM table1 t1 JOIN table3 t3 JOIN table2 t2

bit more detail: (using on as a where clause to filder based on specific date)

     SELECT * FROM table1 t1 JOIN table3 t3 JOIN table2 t2 ON t3.DATE=t2.DATE

How to union multiple table in MYSQL

2 Answers2