Joining 2 tables but only show values from 2nd table once in 1st table

I’ve been racking my brain trying to solve this problem. I’m using Presto SQL.

I have 2 tables:

trx (transactions per day):

| date       | usage    | deposit_id |
| -----------| -------- |------------|
| 2024-08-12 | 2,500    | DEP001     |
| 2024-08-14 | 3,500    | DEP002     |
| 2024-08-15 | 3,500    | DEP002     |

deposit (customers doing deposit):

| deposit_date | deposit_id | deposit_amount|
| -------------| -----------|---------------|
| 2024-08-12   | DEP001     | 1,000,000     |
| 2024-08-13   | DEP002     |  500,000      |

I want the joined tables to be (transactions coming from each deposit):

| date       | usage    | deposit_id | deposit_amount
| -----------| -------- |------------|---------------
| 2024-08-12 | 2,500    | DEP001     |   1,000,000
| 2024-08-13 |   0      | DEP002     |    500,000
| 2024-08-14 | 3,500    | DEP002     |      0
| 2024-08-15 | 3,500    | DEP002     |      0

Basically I want the 2 tables to be joined and show usage when on the date where is usage, and deposit_amount on the date where there is a deposit. And I also want to keep on tracking the deposit_id for each usage.

Left/Right/Inner Join wont work as there would be dates not showing.

Please help! Thank you

I’ve tried left, right, inner join but it won’t work. Also explored union all and group by but it would show blanks in the final table.

You need to sign in to view this answers