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

MySQL select from multiple tables, keep all columns and row without match

I have 2 tables

tableA:

id dateA colA …
1 2022-11-11 12:00:00 A
2 2022-11-12 12:00:00 B
3 2022-11-14 12:00:00 C

tableB:

id dateB colB …
3 2022-11-05 12:00:00 D
4 2022-11-06 12:00:00 E
5 2022-11-13 12:00:00 F

and I want put all rows to one result and sort it by column date

Wanted result (rows from both tables sorted by column date DESC):

id date colA colB … …
3 2022-11-14 12:00:00 C
5 2022-11-13 12:00:00 F
2 2022-11-12 12:00:00 B
1 2022-11-11 12:00:00 A
4 2022-11-06 12:00:00 E
3 2022-11-05 12:00:00 D

I can combine tables, but tables are "squashed"…

SELECT 
    COALESCE(a.id, b.id) AS id, 
    COALESCE(a.dateA, b.dateB) AS date, 
    a.colA, 
    b.colB
FROM tableA AS a, tableB AS b
ORDER BY date DESC
Tags:

2

Answers


  1. 0

    Use UNION ALL and ORDER BY. This requires enumerating the columns:

    select id, dateA as dateX, colA, null as colB from tableA
union all
select id, dateB, null, colB from tableB
order by dateX

    union all combines two datasets. Since we have different columns in the two tables, we need to arrange both select clauses so that they return the same set of columns ; null values can be used to fill in "missing" columns in a given dataset.

    As for sorting the resultset : in MySQL, a single ORDER BY in a UNION ALL query applies to the whole resultset (after it was unioned). The query just does that (using dateX, the column alias that was created in the subqueries).

    Login or Signup to reply.
  2. 0

    In case you want to show the rows that share same id and date as one row, you can build on top of GMB’s answer:

    select id, dateX as date, max(colA) as colA, max(colB) as colB
from (
  select id, dateA as dateX, colA, null as colB from tableA
  union all
  select id, dateB, null, colB from tableB
) as q
group by id, dateX
order by dateX

    See db-fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search