Skip to content
Advertisement

MySQL Query to combine two tables

I’m trying to get a MySQL query to combine two tables. Both tables have a user_id and date column and the other columns are unique to each table. What I’m looking to accomplish is best explained with an image (imgur). I’ve tried various joins and unions and can’t get the right combination. I’ve also tried searching stackoverflow but I’m likely not using the right terminology for what I’m looking to do.

The description of the tables in TEXT:

tablel 
user_id date       animals 
1       9/15/2020  Lion 
2       8/11/2020  Tiger 
3       12/12/2020 Bear 

table2 
user_id date      fruit 
1       7/7/2020  apple 
2       6/5/2020  pear 
4       3/8/2020  peach

output 
user_id date        animals fruit 
1       9/15/2020   Lion 
1       7/7/2020            apple 
2       8/11/2020   Tiger 
2       6/5/2020            pear
3       12/12/2020  Bear 
4       3/8/2020            peach 

Advertisement

Answer

With UNION ALL you can achieve that

SELECT user_id, date, animals, fruit FROM
(
      SELECT user_id, date, animals, '' as fruit
        FROM table1
      UNION ALL
      SELECT user_id, date, '', fruit
        FROM table2
 ) t
 ORDER BY user_id, animals
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement