By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl1(cd int, productcd int, type varchar(2));
create table tbl2(cd int, productcd int, type varchar(2), priority int);
insert into tbl2 values
(1, 1, 'A' , 1),
(1, 2, 'AB' , 2),
(1, 3, 'A' , 3),
(1, 4, 'AB' , 4),
(1, 5, 'AC' , 7),
(2, 1, 'A' , 3),
(2, 3, 'AB' , 4),
(2, 4, 'AC' , 8),
(2, 7, 'HV' , 10),
(3, 1, 'A' , 2),
(3, 2, 'AC' , 3),
(3, 7, 'BC' , 5),
(3, 4, 'E' , 9),
(3, 5, 'T' , 11);
insert into tbl1 values
(1, 1, 'A' ),
(1, 2, 'AB' ),
(1, 3, 'A' ),
(2, 3, 'AB' ),
(2, 4, 'AC' ),
(3, 1, 'A' );
with t as (
select *,
Row_Number() over(
partition by cd
order by
case when exists (
select * from tbl1 where tbl1.cd=tbl2.cd and tbl1.productcd=tbl2.productcd
) then 0 else 1
end, priority) rn
from tbl2
)
select cd, productcd, type
from t
where rn<=4
| cd | productcd | type |
|---|---|---|
| 1 | 1 | A |
| 1 | 2 | AB |
| 1 | 3 | A |
| 1 | 4 | AB |
| 2 | 3 | AB |
| 2 | 4 | AC |
| 2 | 1 | A |
| 2 | 7 | HV |
| 3 | 1 | A |
| 3 | 2 | AC |
| 3 | 7 | BC |
| 3 | 4 | E |