Question 220946 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Join two tables, remove duplicate rows and sort two date columns

I’m trying to union two tables and then create one line of information for both of then.

My code is:

with cte as (
   select id,
          dt_month_transaction,
          dt_first_transaction,
          transaction_total,
          null as dt_month_redemption,
          null as redemption_total
  from tb_transaction
union 
 select id,
        null as dt_month_transaction,
        null as dt_first_transaction,
        null as transaction_total,
        dt_month_redemption,
        redemption_total
  from tb_redemption
)

Then, the output is:

id dt_month_transaction dt_first_transaction transaction_total dt_month_redemption redemption_total
1 2023-04-01 2019-10-01 27.508
1 2022-04-01 108.0
2 2023-04-01 2019-10-01 26.308
2 2022-04-01 108.0

But it should be like this:

id dt_month_transaction dt_first_transaction transaction_total dt_month_redemption redemption_total
1 2023-04-01 2019-10-01 27.508 2022-04-01 108.0
2 2023-04-01 2019-10-01 26.308 2022-04-01 108.0

There’s a way to perform what I’m trying to do?

Tags:

3

Answers


  1. 0

    join, not union. Then simply select the columns mix and match as desired from both tables. "INNER" join picks up only records from both tables that have matching keys.

    "LEFT JOIN" picks up all records from tb_transactions (A) and only matching records from tb_redemption (B). When no matching B record, selected B columns will be null

    select
  A.dt_month_transaction,
  A.dt_first_transaction,
  A.transaction_total,
  A.dt_month_redemption,
  A.redemption_total
  B.dt_month_redemption,
  B.redemption_total
from from tb_transaction  A
inner join tb_redemption B on A.keyA = B.keyB
    Login or Signup to reply.
  2. 0

    Assuming id is the correlating column and you need data for those id values only which are present in both tables, you can try to use join as below

    select tr.id,
tr.dt_month_transaction,
tr.dt_first_transaction,
tr.transaction_total,
red.dt_month_redemption,
red.redemption_total
from tb_transaction tr
inner join tb_redemption red on tr.id = red.id
    Login or Signup to reply.
  3. 0

    UNION does not merge rows.
    Seems like you want a FULL [OUTER] JOIN:

    SELECT id
     , t.dt_month_transaction
     , t.dt_first_transaction
     , t.transaction_total
     , r.dt_month_redemption
     , r.redemption_total
FROM   tb_transaction     t
FULL   JOIN tb_redemption r USING (id)
ORDER  BY id;  -- seems like you want the result ordered this way

    This preserves all rows, even if there is no match in the other table. The USING clause in the join condition simplifies the query. You always get an id without the need for COALESCE(t.id, r.id).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search