MySQL Merge two queries based on mutual column

I have two queries that retrieve records from 2 different tables that are almost alike and I need to merge them together.

Both have created_date which is of type datetime and I’m casting this column to date because I want to group and order them by date only, I don’t need the time.

First query:

select cast(created_date as date) the_date, count(*)
from question
where user_id = 2
group by the_date
order by the_date;
+------------+----------+
| the_date   | count(*) |
+------------+----------+
| 2021-01-02 |        1 |
| 2021-02-10 |        1 |
| 2021-02-14 |        5 | -- this line contains a mutual date
| 2021-03-16 |        1 |
| 2021-03-26 |        3 |
| 2021-03-27 |       23 |
| 2021-03-28 |        5 |
| 2021-03-29 |        1 |
+------------+----------+

Second query:

select cast(created_date as date) the_date, count(*)
from answer
where user_id = 2
group by the_date
order by the_date;

+------------+----------+
| the_date   | count(*) |
+------------+----------+
| 2021-02-08 |        2 |
| 2021-02-14 |        1 | -- this line contains a mutual date
| 2021-04-05 |        5 |
| 2021-04-06 |        2 |
+------------+----------+

What I need is to merge them like this:

+------------+---------------+---------------+
| the_date   | count(query1) | count(query2) |
+------------+---------------+---------------+
| 2021-01-02 |             1 |             0 | -- count(query2) is 0 bc. it's not in the second query
| 2021-02-08 |             0 |             2 | -- count(query1) is 0 bc. it's not in the first query
| 2021-02-10 |             1 |             0 | 
| 2021-02-14 |             5 |             1 | -- mutual date
| 2021-03-16 |             1 |             0 |
| 2021-03-26 |             3 |             0 |
| 2021-03-27 |            23 |             0 |
| 2021-03-28 |             5 |             0 |
| 2021-03-29 |             1 |             0 |
| 2021-04-05 |             0 |             5 |
| 2021-04-06 |             0 |             2 |
+------------+---------------+---------------+

Basically what I need is to have all dates together and for each date to have the corresponding values from those two queries.

Answer

try something like this.

SELECT the_date , max(cnt1) , max(cnt2)
FROM (
  select cast(created_date as date) the_date, count(*) AS cnt1 , 0 as cnt2
  from question
  where user_id = 2
  group by the_date
  order by the_date
UNION ALL
 select cast(created_date as date) the_date, 0, count(*)
 from answer
 where user_id = 2
 group by the_date
 order by the_date
) as t1
GROUP BY the_date
ORDeR BY the_date;

Advertisement

Answer