Join tables showing unique values in each row

Tahir Zamaan 0
Viewed 32

I have 2 tables called cash and check

CASH

cstm_id cash_date cash_amount
101 20220529 50
101 20220530 100
102 20220601 50
102 20220603 75

CHECK

cstm_id check_date check_amount
101 20210525 150
101 20210812 25
102 20220210 25
102 20220603 20

I want to join the tables so I have unique values in each row without any duplication

EXPECTED RESULT:

cstm_id cash_date cash_amount check_date check_amount
101 20220529 50 null null
101 20220530 100 null null
101 null null 20210525 150
101 null null 20210812 25
102 20220601 50 null null
102 null null 20210110 25
102 20220603 75 20220603 20

What would be the most appropriate sql script for this output

database-design join mysql sql
1 Answers

One approach can be via full outer join -

select distinct * from (
   select c.*, c1.check_date, c1.check_amount
      from cash c left join check1 c1
      on c.cstm_id=c1.cstm_id
      and c.cash_date = c1.check_date
  union all
    select c1.cstm_id,c.cash_date,c.cash_amount, c1.check_date, c1.check_amount
      from cash c right join check1 c1
      on c.cstm_id=c1.cstm_id
      and c.cash_date = c1.check_date
 ) t;

Fiddle.

user92oW0
Related