Postgresql – How to match one row in outer join

postgresql

I have a situation where incoming rows (src) need to be matched to rows in a target table (tgt). However, each target row can only be matched once for all the incoming source rows. Example SQL follows:

drop table if exists src;
create table src(id int not null, join_key text not null);
insert into src(id, join_key) values
(1,'X'),
(2,'X'),
(3,'X'),
(4,'Y'),
(5,'Y')
;

drop table if exists tgt;
create table tgt(id int not null, join_key text not null);
insert into tgt(id, join_key) values
(10,'X'),
(20,'X'),
(40,'Y')
;

with a as (
    select s.id as src_id,
           s.join_key,
           t.id as tgt_id
    from src s
             left join tgt t on s.join_key = t.join_key
),b as (
    select *,
           row_number() over (partition by src_id order by tgt_id) as rw
    from a
)
     select src_id, tgt_id from b
 where rw = 1
;

This gives:

1,10
2,10
3,10
4,40
5,40

But the values should be:

1,10
2,20
3,null
4,40
5,null

How can I do this?

Best Answer

WITH
cte1 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM src ),
cte2 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM tgt )
SELECT cte1.id src_id, cte2.id tgt_id, COALESCE(cte1.join_key, cte2.join_key) join_key
FROM cte1
FULL OUTER JOIN cte2 ON cte1.join_key = cte2.join_key AND cte1.rn = cte2.rn

fiddle

Related Question