Question 292579 in Mysql Question posted in
The official MySQL documentation can be found here.

Combining the result of 3 MySQL tables into one result in PHP sorted by created_at

Let’s say I have 3 DB tables in MySQL:

t1
t2
t3

And each of them has the same columns I am interested in.

And also each of them has some other columns that I am not interested in.

Now, the common columns in all of them I want to put into the final result are name, age, created_at.

And I want to grab all the data (we are not concerned about the performance – it’s an intranet application, so, 2 or 3 seconds per query is nothing of concern) and sort them by created_at while creating the result.

So, when accessing the result in PHP and doing a while loop the output will be already sorted chronologically.

How to do that in MySQL?

Tags:

2

Answers


  1. 0

    You’ll want to use a UNION query. This allows you to combine the results of multiple SELECT statements into a single result set, which can then be ordered as you require. 

    (SELECT name, age, created_at FROM t1)
UNION
(SELECT name, age, created_at FROM t2)
UNION
(SELECT name, age, created_at FROM t3)
ORDER BY created_at;

    Note that UNION by default removes duplicate rows. If you want to include duplicates, use UNION ALL instead.

    Ensure that the columns you’re combining with UNION have compatible data types across all tables.

    Login or Signup to reply.
  2. 0

    With UNION you can put several tables in one query, but the result is handled as one.

    Here an example

    Table 1

    name age created_at
    1 1 1
    2 2 2

    Table 2

    name age created_at
    3 3 3
    5 5 5

    Table 3

    name age created_at
    4 4 4
    6 6 6 
    SELECT name, age, created_at FROM t1
UNION SELECT name, age, created_at FROM t2
UNION SELECT name, age, created_at FROM t3
ORDER BY created_at;
    name age created_at
    1 1 1
    2 2 2
    3 3 3
    4 4 4
    5 5 5
    6 6 6
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search