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